Oracle SQL group by

Question

I have below data in my table,

    Id month value

    1  Jun  20
    1  Jul  22
    1  Aug  0
    1  Sep  12
    2  Jun  21
    2  Jul  45

I need to group them as

Id Jun Jul Aug Sep
1   20  22 0   12
2   21  45

I am not sure what syntax i should use. Help appreciated

Are the months fixed (Jun, Jul, Aug and Sep ONLY)? Also, do you need to identify which months they are (that is, which year they are in)? — user5683823
– user5683823, Commented Dec 1, 2016 at 17:54

Yuriy Tsarkov · Accepted Answer · 2016-12-01 17:53:15Z

2

@Gurwinder's answer is workable. Also you can use pivot (in Oracle 11 or above):

select *
 from (
  select id, month, value
    from your_table    
)
pivot (
      max(value)
      for month in ('Jun' as "Jun", 'Jul' as "Jul", 'Aug' as "Aug", 'Sep' as "Sep")
)t

edited Dec 1, 2016 at 17:53

user5683823

answered Dec 1, 2016 at 17:51

Yuriy Tsarkov

2,5882 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user5683823 Over a year ago

@Pat - in both solutions, if your "input" table is already the result of a query where you use GROUP BY and COUNT() or SUM(), you can get a simpler result if you combine the "pivoting" (either done manually as in Gurwinder's answer or using the Oracle 11 and above PIVOT operation as in Yuriy's answer) with the grouping and counting/summing. Both methods allow you to group, count/sum AND pivot all at once. For more help, if this is your case, show the original query that produces your "inputs".

Gurwinder Singh Over a year ago

What made you use max(value)?

Yuriy Tsarkov Over a year ago

Whereas pivot requires some aggregate function, it's just a trick make it do its work. In your case you can use sum or another operator as well.

Gurwinder Singh · Accepted Answer · 2016-12-01 17:36:24Z

1

Please try below:

select id,
    sum(decode(month, 'Jun', value)) jun,
    sum(decode(month, 'Jul', value)) jul,
    sum(decode(month, 'Aug', value)) aug,
    sum(decode(month, 'Sep', value)) sep
from my_table group by id

answered Dec 1, 2016 at 17:36

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Comments

Tommy Clark · Accepted Answer · 2016-12-01 18:15:08Z

You did not mention the version of Oracle. If you are using Oracle 11g or above, the following example would get you close.

create table pivot_test (id number(1), month varchar2(3), value number(2));

insert into pivot_test values (1, 'Jun', 20);
insert into pivot_test values (1, 'Jul', 22);
insert into pivot_test values (1, 'Aug', 0);
insert into pivot_test values (1, 'Sep', 12);
insert into pivot_test values (2, 'Jun', 21);
insert into pivot_test values (2, 'Jul', 45);
commit;

select *
from (select id, month, sum(value) as value from pivot_test group by id, month)
pivot (SUM(value) for (month) in ('Jun', 'Jul', 'Aug', 'Sep'))
order by id
;

It yields the following result.

        ID      'Jun'      'Jul'      'Aug'      'Sep'
---------- ---------- ---------- ---------- ----------
         1         20         22          0         12
         2         21         45

Collectives™ on Stack Overflow

Oracle SQL group by

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related