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