SQL select aggregate values in columns

Question

I have a table in this structure:

editor_id
rev_user
rev_year
rev_month
rev_page
edit_count

here is the sqlFiddle: http://sqlfiddle.com/#!2/8cbb1/1

I need to surface the 5 most active editors during March 2011 for example - i.e. for each rev_user - sum all of the edit_count for each rev_month and rev_year to all of the rev_pages.

Any suggestions how to do it?

UPDATE - updated fiddle with demo data

please specify your records in sql fiddle. – unknown May 25 '14 at 16:03 — unknown, May 25 '14 at 16:03

score 1 · Answer 1 · answered May 25 '14 at 16:10

1

You should be able to do it like this:

Select the total using SUM and GROUP BY, filtering by rev_year and rev_month
Order by the SUM in descending order
Limit the results to the top five items

Here is how:

SELECT * FROM (
    SELECT rev_user, SUM(edit_count) AS total_edits
    FROM edit_count_user_date
    rev_year='2006' AND rev_month='09'
    GROUP BY rev_user
) x
ORDER BY total_edits DESC
LIMIT 5

Demo on sqlfiddle.

answered May 25 '14 at 16:10

Sergey Kalinichenko

714,442
84
1,110
1,523

OK Thanks, now if I need to provide list of rev_pages edited by the most active editor, along with the number of editing acts he has invested at each document. i.e. I need to find the most active editor (sum 'edit_count' of rev_page of each year and month) and then find all the pages he edited. how can I do it? – user3426610 May 25 '14 at 16:33

score 0 · Answer 2 · edited May 23 '17 at 12:05

Surely this is as straightforward as :

SELECT rev_user, SUM(edit_count) as TotalEdits
FROM edit_count_user_date
WHERE rev_month = 'March' and rev_year = '2014'
GROUP BY rev_user
ORDER BY TotalEdits DESC
LIMIT 5;

SqlFiddle here

May I also suggest using a more appropriate DATE type for the year and month storage?

Edit, re new Info

The below will return all edits for the given month for the 'highest' MonthTotal editor, and then re-group the totals by the rev_page.

SELECT e.rev_user, e.rev_page, SUM(e.edit_count) as TotalEdits
FROM edit_count_user_date e
INNER JOIN 
(
  SELECT rev_user, rev_year, rev_month, SUM(edit_count) AS MonthTotal
  FROM edit_count_user_date
  WHERE rev_month = '09' and rev_year = '2010'
  GROUP BY rev_user, rev_year, rev_month
  ORDER BY MonthTotal DESC
  LIMIT 1
) as x
ON e.rev_user = x.rev_user AND e.rev_month = x.rev_month AND e.rev_year = x.rev_year
GROUP BY e.rev_user, e.rev_page;

SqlFiddle here - I've adjusted the data to make it more interesting. However, if you need to do this across several months at a time, it will be more difficult given MySql's lack of partition by / analytical windowing functions.

OK Thanks, now if I need to provide list of rev_pages edited by the most active editor, along with the number of editing acts he has invested at each document. i.e. I need to find the most active editor (sum 'edit_count' of rev_page of each year and month) and then find all the pages he edited. how can I do it? — user3426610, May 25 '14 at 16:33
I've updated for one month, although see the caveat if you intend doing this across multiple months in one go. — StuartLC, May 25 '14 at 16:52

SQL select aggregate values in columns

2 Answers2