Marek Janoud Marek Janoud - 2 months ago 7
MySQL Question

MySQL using group function inside group function

my database:

+++++++++++++++++++++++++++++++++++++
+ id | group_id | inside_id | value +
+ 1 | 1 | 1 | 50 +
+ 2 | 1 | 2 | 12 +
+ 3 | 1 | 1 | 4 +
+ 4 | 2 | 3 | 140 +
+ 5 | 2 | 2 | 81 +
+ 6 | 2 | 3 | 24 +
+++++++++++++++++++++++++++++++++++++


I want to do something like this:

SELECT group_id, SUM(CASE WHEN MAX(inside_id) = inside_id THEN value ELSE 0 END) as sum_value FROM table GROUP BY group_id


Expected result:

++++++++++++++++++++++++
+ group_id | sum_value +
+ 1 | 12 +
+ 2 | 164 +
++++++++++++++++++++++++


I use this query in left join and thats why i dont know before, which values "inside_id" contains and I just need the sum of value when "inside_id" is maximal in current group, problem is that group function inside group function not work and cause "Invalid use of group function".

Btw "inside_id" for "group_id" contains only two posibilities.

Answer

Try something like this

SELECT group_id, SUM(value) as sum_value 
FROM table A
Where inside_id = (select max(inside_id) from table B where a.group_id=b.group_id)
GROUP BY group_id

The sub-query will find the max inside_id for each group_id

Another approach using correlated sub-query and conditional aggregate some what similar to your current try.

SELECT group_id, 
       Sum(CASE 
             WHEN inside_id = (SELECT Max(inside_id) 
                               FROM   table B 
                               WHERE  a.group_id = b.group_id) THEN value 
             ELSE 0 
           END) AS sum_value 
FROM   table A 
GROUP  BY group_id 
Comments