SQL Query - SUM of Top 3 and GROUP By Value

Question

Being quite new to SQL I'm struggling with a query.

My database contains the columns ID, WEIGHT, TEAM

I want to return the results of the top 3 WEIGHTS per ID, SUM them together and group by TEAM. Every other entry for ID would be ignored. So the results would read TEAM WEIGHT.

SELECT id, team, SUM( weight ) AS total
FROM (

SELECT id, team, weight
FROM database m
WHERE (

SELECT COUNT( * ) 
FROM database mT
WHERE 
mT.id = m.id
AND mT.weight >= m.weight
) <=3
)tmp

GROUP BY team
ORDER BY total DESC

This is my effort but its definitely not right. Sorry if this is a basic query but I have researched online with no joy. I would appreciate any help as I'm a little stuck.

Table

ID    Weight    Team
1     100       A
5     200       C *
1     300       A *
1     100       A
3     50        
1     200       A *
5     20        C
1     150       A *
5     200       C *
4     50        B *
5     200       C *
6     100       A *
2     50        C
6     100       A *
2     100       C *
6     100       A *
2     100       C *
6     50        A 
2     200       C *

The expected results would be:

Team   Total
C      1000
A      950
B      50

I have starred the values that should be summed (top 3 per ID grouped by Team). A point to note is that it's not the top 6 per team. It has to be the top 3 per ID added together, then the top 3 of different IDs added again to make the team total.

On mobile so will have to explain expected result. If ID 1 and ID 5 are TEAM A the query will sum the top 3 weights for each ID and sum them together to produce a TEAM score. So.. if ID 1 has 5 entries weights of 10, 50, 100, 60, 20 and ID 5 has 4 weight entries of 20, 100, 350, 40. Team A score would be 700. ID 1(50+100+60) + ID 5 (100+350+40) — Mark Harraway, May 22 '18 at 20:58
From the answers provided thus far, you will at least have seen how you might improve your question — Strawberry, May 22 '18 at 23:10

score 0 · Answer 1 · answered May 22 '18 at 19:12

0

How's this?

select team, sum(weight) from (
    select id, team, weight, row_number() over(partition by id order by weight desc) rownum from (
    select id, team, sum(weight) weight
    from database
    group by id, team)a)b
    where rownum<=3
    group by team

answered May 22 '18 at 19:12

Daniel Marcus

2,686
1
7
13

I don't believe MySQL supports row_number() – Error_2646 May 22 '18 at 19:13
Think it depends what version – Daniel Marcus May 22 '18 at 19:14
doesn't work. Gives me a syntax error in MySQL near over(partition) :( – Mark Harraway May 22 '18 at 19:18
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by id order by weight desc) rownum from ( select id, team, sum' at line 2 – Mark Harraway May 22 '18 at 19:25
I have updated the original question with expected results. – Mark Harraway May 23 '18 at 16:46
Does your version support windowed functions like row_number, rank etc? – Daniel Marcus May 23 '18 at 16:57

Error_2646 · Accepted Answer · 2018-05-24T20:42:48.833

0

Hopefully this does the trick.

CREATE TABLE TEST_DATA
  ( id VARCHAR(10),
    weight INTEGER,
    team VARCHAR(10)
  );

INSERT INTO TEST_DATA VALUES('5',200,'C');
INSERT INTO TEST_DATA VALUES('1',300,'A');
INSERT INTO TEST_DATA VALUES('1',100,'A');
INSERT INTO TEST_DATA VALUES('3',50,'');
INSERT INTO TEST_DATA VALUES('1',200,'A');
INSERT INTO TEST_DATA VALUES('5',20,'C');
INSERT INTO TEST_DATA VALUES('1',150,'A');
INSERT INTO TEST_DATA VALUES('5',200,'C');
INSERT INTO TEST_DATA VALUES('4',50,'B');
INSERT INTO TEST_DATA VALUES('5',200,'C');
INSERT INTO TEST_DATA VALUES('6',100,'A');
INSERT INTO TEST_DATA VALUES('2',50,'C');
INSERT INTO TEST_DATA VALUES('6',100,'A');
INSERT INTO TEST_DATA VALUES('2',100,'C');
INSERT INTO TEST_DATA VALUES('6',100,'A');
INSERT INTO TEST_DATA VALUES('2',100,'C');
INSERT INTO TEST_DATA VALUES('6',50,'A');
INSERT INTO TEST_DATA VALUES('2',200,'C');

 SELECT team,
        SUM(weight) AS total_weight
   FROM   (   SELECT id,
                     CASE WHEN @ID = ID THEN @ROW_NUMBER := @ROW_NUMBER + 1
                          ELSE @ROW_NUMBER := 1     
                      END AS rn,
                     team,
                     weight,
                     @id := id
                FROM TEST_DATA,
                     (SELECT @ROW_NUMBER := 1, @ID := '') r
               ORDER 
                  BY id, 
                     weight DESC
          ) TMP
 WHERE rn <= 3
   AND team <> ''
 GROUP
    BY team
 ORDER
    BY total_weight DESC;

edited May 24 '18 at 20:42

answered May 22 '18 at 19:33

Error_2646

2,555
1
10
22

Thanks mate. Definitely a step in the right direction. Doesn't seem to be grouping by team though. It seems to be returning the top 3 summed together for each ID. – Mark Harraway May 22 '18 at 19:49
Does the edit do what you are looking for? It would help if you provided sample input and output data, aggregating by team but still including the id in the result is counter intuitive. – Error_2646 May 22 '18 at 19:51
The query is for a scoring system. Not all ID's have a TEAM value. Your query above just returns the sum of all weights with no team names or groupings. – Mark Harraway May 22 '18 at 20:04
Probably best to add that an ID will only be linked to 1 TEAM. 2 IDs per TEAM. – Mark Harraway May 22 '18 at 20:14
Here's an example of a good sample input expected result question: https://stackoverflow.com/questions/14043162/how-to-query-data-from-sql-server meta post: https://stackoverflow.com/help/mcve – Error_2646 May 22 '18 at 20:55
@MarkHarraway I know it makes sense to you, but from an outside perspective it's difficult to know what you need based on a query that's not doing what you need it to. – Error_2646 May 22 '18 at 20:56
I appreciate your help mate. I’ll post expected results tomorrow. Please see my comment with the text explanation in the main question. – Mark Harraway May 22 '18 at 21:05
I have updated the original question with expected results. – Mark Harraway May 23 '18 at 16:45
@MarkHarraway Check out the answer now. – Error_2646 May 24 '18 at 20:43
@MarkHarraway It's a bit of voodoo SQL that I wouldn't be smart enough to come up with. Credit to Daniel Vassallo https://stackoverflow.com/questions/3162389/multiple-ranks-in-one-table/ – Error_2646 May 24 '18 at 20:46
@MarkHarraway Basically, you initialize a variable as a place holder for the previous record in the partition, incrementing the counter each time it's unchanged, setting it back to 1 when it does. Since we've ordered by id and weight, this mimics the row_number() function from the other answer. In newer versions of MySQL apparently they've added these windowed functions ... which makes life a lot easier. – Error_2646 May 24 '18 at 20:53
Thanks mate. I’ll try the query tomorrow when back at the desk. Looks promising though. – Mark Harraway May 24 '18 at 20:56

SQL Query - SUM of Top 3 and GROUP By Value

2 Answers2