Page MenuHomePhabricator

DBQ-55 the query of count the number of edit
Closed, ResolvedPublic

Description

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.


Version: unspecified
Severity: major

Details

Reference
bz59310

Event Timeline

bzimport raised the priority of this task from to Needs Triage.Nov 22 2014, 2:22 AM
bzimport set Reference to bz59310.

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: