Last modified: 2014-01-03 15:45:11 UTC
This issue was converted from https://jira.toolserver.org/browse/DBQ-55. Summary: the query of count the number of edit Issue type: Task - A task that needs to be done. Priority: Major Status: Done Assignee: Lars Age Kamfjord <lars-toolserverwiki@kamfjord.org> ------------------------------------------------------------------------------- From: zeyi He <wikipediathinker@googlemail.com> Date: Wed, 17 Dec 2008 16:47:57 ------------------------------------------------------------------------------- I have a database with en_sub_meta_history. What i try to do is the table with the number of edit. I don't need this numble of edit linked with the particuler rev_user who did them. only i need is the single row of number of edit in different year. How can i do this query? I tried by my own but failed. thanks for help.
------------------------------------------------------------------------------- From: Lars Age Kamfjord <lars-toolserverwiki@kamfjord.org> Date: Tue, 10 Feb 2009 21:05:23 ------------------------------------------------------------------------------- Not sure what you want here. Do you want just a query with year and total number of edits of all users this year, or username, year and users number of edits? Username,total edits, year will be this query: SELECT u.user_name,COUNT![][1] AS amount,LEFT(rev_timestamp,4) AS year FROM revision r JOIN user u ON u.user_id=rev_user WHERE u.user_name = 'Laaknor' GROUP BY LEFT(rev_timestamp,4),u.user_name; (just take out "WHERE u.user_name = 'Laaknor' to get all users Or if you wish to have just the total amount of edits group by year, it's much easier: SELECT COUNT![][1] AS amount,LEFT(rev_timestamp,4) FROM revision GROUP BY LEFT(rev_timestamp,4); Does this help? Edit: Bah, replace ![][1] with ( and * and ) [1]: https://jira.toolserver.org/images/icons/emoticons/star_yellow.gif
------------------------------------------------------------------------------- From: zeyi He <wikipediathinker@googlemail.com> Date: Wed, 18 Feb 2009 14:43:17 ------------------------------------------------------------------------------- Hi, Thanks! I think first one is what i need. but only i have is revision table and page table. I don't have user table in my own database server. so this query can't work. can i use rev_user in revision table to instead of user_id in user table? thanks.
------------------------------------------------------------------------------- From: Lars Age Kamfjord <lars-toolserverwiki@kamfjord.org> Date: Thu, 19 Feb 2009 17:09:09 ------------------------------------------------------------------------------- I've run the query and put it on http://toolserver.org/~laaknor/SQL/DBQ-55.txt It's rather large, so I hope it helps ![][1] [1]: https://jira.toolserver.org/images/icons/emoticons/wink.gif
This bug was imported as RESOLVED. The original assignee has therefore not been set, and the original reporters/responders have not been added as CC, to prevent bugspam. If you re-open this bug, please consider adding these people to the CC list: Original assignee: (none) CC list: