CODEWITHSUNDEEP
sqloracle-databasehibernategroup-by
faraa
faraa

Reputation: 575

group by conditional on two columns in hibernate

I want to group by on two columns. I want to get total of c group by a and b if b is not null and group by a if b is null I wrote this query but it does not work in case b is null!the result of query is all rows that b is not null

select  m.s.a ,
case when (m.l is not  null)  
then  m.l.code end , coalesce(sum(m.c),0  ) 
from material m where m.Item.id =:itemId 
group by m.s.a, case 
when (m.l is not null)  
then m.l.code end


+--+----+-------+---+
|  | s  | l     | c |
+--+----+-------+---+
|  |  a | d     |  1 |
|  |  a | d     |  9 |
|  |  a | e     |  3 |
|  |  a | f     |  4 |
|  |  c | g     |  5 |
|  |  c | g     |  6 |
|  |  c | h     | 20 |
|  |  d | null  |  7 |
|  |  d | null  |  8 |

result expected:

+--+----+-------+---+
|  | s  | l     | c |
+--+----+-------+---+
|  |  a | d     | 10 |
|  |  a | e     |  3 |
|  |  a | f     |  4 |
|  |  c | g     | 11 |
|  |  c | h     | 20 |
|  |  d |       | 15 |

Upvotes: 0

Views: 325

Answers (2)

Slkrasnodar
Slkrasnodar

Reputation: 824

with data (col1, col2, val) as
(
  select 'a',   'd',        1 from dual union all  
  select 'a',   'd',        9 from dual union all  
  select 'a',   'e',        3 from dual union all  
  select 'a',   'f',        4 from dual union all  
  select 'c',   'g',        5 from dual union all  
  select 'c',   'g',        6 from dual union all  
  select 'c',   'h',       20 from dual union all  
  select 'd',   null,       7 from dual union all  
  select 'd',   null,       8 from dual union all
  select 'e',   'g',     null from dual  -- additional check if val is null
)
,
prs (col1, col2, col1n2) as 
(
  select distinct col1, col2, col1||'-'||col2 from data
)
,
rs (col, val) as 
(
  -- concatenate the columns that need to be grouped by 
  -- to act as one single column (col1 and col2)
  select col1||'-'||col2, sum(nvl(val,0)) from data group by col1||'-'||col2
)
select 
  prs.col1, prs.col2, rs.val 
from 
  rs join prs 
  on (prs.col1n2 = rs.col)
order by 1
;

Upvotes: 0

ansh
ansh

Reputation: 696

By default, oracle/postgres/mysql will produces the expected output.

SELECT s,l,sum(c)
FROM temp
GROUP BY s,l;

If you don't want to group by NULL values you can use UNION

SELECT s,l,sum(c)
FROM temp
WHERE l is NOT NULL
GROUP BY s,l
UNION
SELECT s,l,sum(c)
FROM temp
WHERE l is NULL;

Upvotes: 2

Related Questions