using group function inside group function

Question

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 that's why I don't 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".

By the way inside_id for group_id contains only two possibilities.

Answer 1

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 

Answer 2

Here is an option which uses a join with an uncorrelated subquery to identify, and then sum, records having the max inside_id for each group.

SELECT t1.group_id, SUM(t1.value) AS sum_value
FROM yourTable t1
INNER JOIN
(
    SELECT group_id, MAX(inside_id) AS inside_id
    FROM yourTable
    GROUP BY group_id
) t2
    ON t1.group_id  = t2.group_id AND
       t1.inside_id = t2.inside_id
GROUP BY t1.group_id