using SUM and DISTINCT in the same query

Question

So I am trying to build a query that will show me which users have the most points, for each type of activity. You can see the table strucutre below. Each activity has an activity_typeid and each of those carries a certain activity_weight.

In the example below, Bob has scored 50 points for calls and 100 points for meetings. James has scored 100 points for calls and 100 points for meetings.

userid     activity_typeid     activity_weight
------------------------------------------------------------
123 (Bob)  8765 (calls)                50
123 (Bob)  8121 (meetings)             100
431 (James) 8765 (calls)               50
431 (James) 8121 (meetings)            100
431 (James) 8765 (calls)               50

I want to be able to output the following:

1. Top Performer for Calls = James
2. Top Performer for Meetings = Bob, James.

I don't know the activity_typeid's in advance, as they are entered randomly, so I was wondering if it is possible to build some sort of query that calculates the SUM for each DISTINCT/UNIQUE activity_typeid ?

Thanks so much in advance.

Answer 1

What you need is equivalent of analytic function DENSE_RANK(). One way to do it in if you need top performers for each activity

SELECT a.activity_typeid, GROUP_CONCAT(a.userid) userid
  FROM
(
  SELECT activity_typeid, userid, SUM(activity_weight) activity_weight
    FROM table1
  -- WHERE ...
   GROUP BY userid, activity_typeid
) a JOIN 
(
  SELECT activity_typeid, MAX(activity_weight) activity_weight
    FROM
  (
    SELECT activity_typeid, userid, SUM(activity_weight) activity_weight
      FROM table1
      -- WHERE ...
     GROUP BY userid, activity_typeid
  ) q
   GROUP BY activity_typeid
) b 
    ON a.activity_typeid = b.activity_typeid
   AND a.activity_weight = b.activity_weight
 GROUP BY activity_typeid

Another way to emulate DENSE_RANK() in MySQL is to leverage session variables

SELECT activity_typeid, GROUP_CONCAT(userid) userid
  FROM
(
  SELECT activity_typeid, userid, activity_weight,
         @n := IF(@g = activity_typeid, IF(@v = activity_weight, @n, @n + 1) , 1) rank,
         @g := activity_typeid, @v := activity_weight
    FROM
  (
    SELECT activity_typeid, userid, 
           SUM(activity_weight) activity_weight
      FROM table1
    -- WHERE ...
     GROUP BY activity_typeid, userid
  ) q CROSS JOIN (SELECT @n := 0, @g := NULL, @v := NULL) i
     ORDER BY activity_typeid, activity_weight DESC, userid
) q
 WHERE rank = 1
 GROUP BY activity_typeid

Output:

| ACTIVITY_TYPEID |  USERID |
|-----------------|---------|
|            8121 | 123,431 |
|            8765 |     431 |

Here is SQLFiddle demo for both queries

Answer 2

You must use the GROUP BY statement to calculate the sum for each user and each activity typeid. Try something like this:

SELECT userid, activity_typeid, SUM(activity_weight)
FROM table
GROUP BY userid, activity_typeid

Then use this as a subquery to determine the top performer for each activity_typeid.

Answer 3

If you want to use an aggregate function, you will need to use GROUP BY, instead of DISTINCT. You would group by userid and activity_typeid, and select SUM(activity_weight).

Answer 4

Sure, just use group by like this :

SELECT t.userid, t.activity_typeid, sum(t.activity_weight) 
FROM YOURTABLE t
GROUP BY t.userid, t.activity_typeid

Answer 5

Query to select the (single) top performer for a given activity:

SELECT userid, activity_typeid, SUM(activity_weight)
FROM ?
WHERE activity_typeid = ?
GROUP BY userid
ORDER BY SUM(activity_weight) DESC
LIMIT 1

You can change the LIMIT to output the top 5 or 10 performers, and then inspect them in code to see which you want to show (e.g. if two people have the same score).