On Thu, Oct 27, 2011 at 12:02:27AM +0200, Platonides wrote: > Andre Koopal wrote: > > The query I grabbed counted just the number of pictures, while I needed > > the number of monuments (unique id's). > > > Are they stored somewhere in the db? Otherwise, which template stores it? > > I did the same kind of queries for Spain, but I had a table with images, > authors, ids... so it resulted in sane queries.
The id is set as the sortkey of hidden category, so you can do: SELECT img_user_text, COUNT(DISTINCT(clB.cl_sortkey)) FROM image JOIN page ON img_name=page_title JOIN categorylinks AS clA ON page_id=clA.cl_from JOIN categorylinks AS clB ON page_id=clB.cl_from WHERE page_namespace=6 AND page_is_redirect=0 AND clA.cl_to='Images_from_Wiki_Loves_Monuments' AND clB.cl_to='Rijksmonumenten_with_known_IDs' and img_timestamp > '20100901020000' and img_timestamp < '20101001020000' GROUP BY img_user_text ORDER BY COUNT(DISTINCT(clB.cl_sortkey)) DESC; Again, this is with the last uploader, not the first, and this is the query for last year, so using the old categories, but just for the idea. Regards, Andre _______________________________________________ Wiki Loves Monuments mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/wikilovesmonuments http://www.wikilovesmonuments.eu
