Mark Bell wrote:
Folks,
This may be answered somewhere but i have not found a reference.
I would like stats or a graph that shows:
Number of unique editors per article X number of articles with that many unique editors.
So for example
number of unique editors Number of articles with that many unique editors 15 356 30 3455
MAYBE this will work (haven't tried it):
SELECT editorcount as 'Number of unique editors', COUNT(rev_page) as 'Number of articles with that many unique editors' FROM ( SELECT rev_page, COUNT(DISTINCT rev_user_text) as editorcount FROM revisions GROUP BY rev_page ) AS editorcounts GROUP BY editorcount ORDER BY 'Number of unique editors';
If this doesn't work (maybe MySQL doesn't support this particular way of nesting queries), then I don't think you can do it in a single SQL query, but here's how you could do it in several:
First, create a new table:
CREATE TABLE editorcounts ( page_id int not null, editorcount int not null );
Now fill the table with data that tells you how many unique editors each article has:
INSERT INTO editorcounts (page_id, count) SELECT rev_page, COUNT(DISTINCT rev_user_text) FROM revisions GROUP BY rev_page;
And finally, query the new table for the data you actually want:
SELECT editorcount as 'Number of unique editors', COUNT(page_id) as 'Number of articles with that many unique editors' FROM editorcounts GROUP BY editorcount ORDER BY editorcount;
You may have to create indexes to speed up this process. You may also have to correct syntax errors if I made any, because I haven't tested this :-)
Timwi