Group by inside group by

Question

Is it possible to perform a group by inside a group by ? Actually I have an existing SQL request that returns some data grouping them by the child id. I want to rectify it and display data as is except for certain information that meet a condition, I want to display them by parent ID. Please note that a parent id may contain several child IDs. Can you help please Let s consider the below table :

parentID childID value YN AB 
1       | 11     |3   |Y  |A 
1       | 12     |2   |Y  |A 
2       | 13     |8   |Y  |B  
3       | 14     |9   |Y  |A 

The actual code returns the values that have Y grouped by cildID. What I want is to always return the value column but with an extra condition : If AB = A then return the sum of value grouped by parentID, if not return it as is (grouped by childid)

Answer 1

You can use a CASE expression on the ChildID column to either return the childID when AB = 'B' or NULL when not, then group by ParentID and the CASE expression:

with YourData(parentID, childID, value, YN, AB) as (
  select 1, 11, 3, 'Y', 'A' from dual union all 
  select 1, 12, 2, 'Y', 'A' from dual union all 
  select 2, 13, 8, 'Y', 'B' from dual union all  
  select 3, 14, 9, 'Y', 'A' from dual 
)
select parentID
     , case ab when 'B' then childID end childID
     , sum(value) value
  from YourData
 group by parentID
     , case ab when 'B' then childID end
 order by parentID, childID;

 PARENTID    CHILDID      VALUE
---------- ---------- ----------
         1                     5
         2         13          8
         3                     9

You can also use a GROUP BY ROLLUP to achieve the same result utilizing an appropriate HAVING clause:

select parentID
     , childID
     , sum(value) value
  from YourData
 group by rollup ((parentID, AB), childID)
having (grouping(childID) = 1 and AB = 'A')
    or (grouping(childID) = 0 and AB = 'B')
 order by parentID, childID;

 PARENTID    CHILDID      VALUE
---------- ---------- ----------
         1                     5
         2         13          8
         3                     9