uih98hsdfhhs8p
uih98hsdfhhs8p

Reputation: 3

Multiple GROUP BY and select value dependent on result of the first GROUP BY

I got the following database table:

combination_id  |   weight  |   group   |   std
-------------------------------------------------
    1           |   50      |   3       |   7   
    2           |   30      |   3       |   19
    3           |   30      |   3       |   19
    4           |   25      |   4       |   7

I group the entries by the columns group and std and sum up the values of the weight column:

SELECT SUM(weight) as weight_sum, group, std FROM weight_table 
WHERE combination_id IN (1, 2, 3) 
GROUP BY group, std
ORDER BY weight_sum DESC;

The result looks like this:

    weight  |   group   |   std
-----------------------------------------------
    60      |   3       |   19  
    50      |   3       |   7
    25      |   4       |   7

Now I want do to a second GROUP BY, but only on the group column, and sum over the weight column. The value of the std column in the result should be the value of the std column of the entry with the highest weight and the same group from the last query. So for group 3 I want the 19 to be selected for std, as 60 was the highest weight for group 3:

    weight  |   group   |   std
-----------------------------------------------
    110     |   3       |   19  
    25      |   4       |   7

How can I achieve this? I am using sqlite 3.

Upvotes: 0

Views: 112

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you want this:

SELECT SUM(weight) as weight_sum, group, max(std) as std FROM weight_table 
WHERE combination_id IN (1, 2, 3) 
GROUP BY group
ORDER BY weight_sum DESC;

In other words, don't think of this as "multiple group bys". Think of it as a single aggregation where you get both the sum of the weights and the maximum value of std.

EDIT:

I seem to have misunderstood the question. This is a bit of a pain in SQL lite. Here is one method:

with w as (
      SELECT SUM(weight) as weight_sum, group, std
      FROM weight_table 
      WHERE combination_id IN (1, 2, 3) 
      GROUP BY group, std
     ),
     wmax as (
      SELECT group, MAX(weight_sum) as maxws
      FROM w
      GROUP BY gruop
     )
select w.group, sum(w.weight_sum) as weight_sum,
       max(case when w.weight_sum = wmax.weight_sum then std end) as std
from w left join
     wmax
     on w.group = wmax.group
group by w.group
order by weight_sum DESC;

Upvotes: 1

Related Questions