Home » SQL & PL/SQL » SQL & PL/SQL » Sum and GroupBy (11.1g)
Sum and GroupBy [message #569402] Thu, 25 October 2012 12:13 Go to next message
primer2020
Messages: 32
Registered: August 2012
Member
Hello,
I'm trying to a sum on a group by. However if you notice there are not data in year 1997 for id 20 and not data in year 1999 for id 21. I would like the sum to come back as empty since there are missing data within the group.

Currently:
GRP CNTY YR SUM
A GER 1996 150000
A GER 1997 130000 =====> Should be null
A GER 1998 170000
A GER 1999 40000 =====> Should be null
A GER 2000 190000
A USA 1996 150000
A USA 1997 160000
A USA 1998 170000
A USA 1999 180000
A USA 2000 190000

create table test   (id integer, wage number, grp varchar2(5), cnty varchar2(5),yr integer);

insert into test values (1,25000,'A','USA',1996);
insert into test values (1,30000,'A','USA',1997);
insert into test values (1,35000,'A','USA',1998);
insert into test values (1,40000,'A','USA',1999);
insert into test values (1,45000,'A','USA',2000);


 
insert into test values (2,125000,'A','USA',1996);
insert into test values (2,130000,'A','USA',1997);
insert into test values (2,135000,'A','USA',1998);
insert into test values (2,140000,'A','USA',1999);
insert into test values (2,145000,'A','USA',2000);



insert into test values (20,25000,'A','GER',1996);
insert into test values (20,35000,'A','GER',1998);
insert into test values (20,40000,'A','GER',1999);
insert into test values (20,45000,'A','GER',2000);


 
insert into test values (21,125000,'A','GER',1996);
insert into test values (21,130000,'A','GER',1997);
insert into test values (21,135000,'A','GER',1998);
insert into test values (21,145000,'A','GER',2000);


select grp, cnty,yr,sum(wage) as wage from test where yr in(1996,1997,1998,1999,2000) group by grp, cnty,yr 
order by grp, cnty,yr;

Re: Sum and GroupBy [message #569405 is a reply to message #569402] Thu, 25 October 2012 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that there is no line for id 10 so all lines should be NULL. Twisted Evil

Regards
Michel
Re: Sum and GroupBy [message #569406 is a reply to message #569402] Thu, 25 October 2012 12:52 Go to previous messageGo to next message
joy_division
Messages: 4512
Registered: February 2005
Location: East Coast USA
Senior Member
You have to group by ID, because there is indeed data for 1997, just in a different ID. If you don't group by ID, you're going to get data.
How is Oracle supposed to know that you expect it to be null when you do have data for another ID?
Re: Sum and GroupBy [message #569407 is a reply to message #569405] Thu, 25 October 2012 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with cnt as (select grp, cnty, count(distinct id) cnt from test group by grp, cnty)
  2  select test.grp, test.cnty, yr, decode(count(*), cnt, sum(wage)) sum_wage
  3  from test, cnt
  4  where cnt.cnty = test.cnty
  5    and cnt.grp = test.grp
  6  group by test.grp, test.cnty, yr, cnt
  7  order by test.grp, test.cnty, yr;
GRP   CNTY          YR   SUM_WAGE
----- ----- ---------- ----------
A     GER         1996     150000
A     GER         1997
A     GER         1998     170000
A     GER         1999
A     GER         2000     190000
A     USA         1996     150000
A     USA         1997     160000
A     USA         1998     170000
A     USA         1999     180000
A     USA         2000     190000

Regards
Michel
Re: Sum and GroupBy [message #569408 is a reply to message #569405] Thu, 25 October 2012 12:56 Go to previous messageGo to next message
primer2020
Messages: 32
Registered: August 2012
Member
Sorry, what do you mean by there is no line for id 10 ?
id=10 does not exist in the whole table.
Re: Sum and GroupBy [message #569412 is a reply to message #569408] Thu, 25 October 2012 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59072
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was a joke.
You said there is no data for id 20 in a specific year so the result should be null for this year, I conclude as there is no data for id 10 in any year all results should be null.
Just a way to show you thet the requirements are not complete.

See my previous answer for a query.

Regards
Michel

[Updated on: Thu, 25 October 2012 13:28]

Report message to a moderator

Re: Sum and GroupBy [message #569417 is a reply to message #569407] Thu, 25 October 2012 14:11 Go to previous message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
Why not simply:

select  grp,
        cnty,
        yr,
        case max(count(distinct id)) over(partition by grp,cnty)
          when count(distinct id) then sum(wage)
        end sum_wage
  from  test
  group by  grp,
            cnty,
            yr
  order by grp,
           cnty,
           yr
/

GRP   CNTY          YR   SUM_WAGE
----- ----- ---------- ----------
A     GER         1996     150000
A     GER         1997
A     GER         1998     170000
A     GER         1999
A     GER         2000     190000
A     USA         1996     150000
A     USA         1997     160000
A     USA         1998     170000
A     USA         1999     180000
A     USA         2000     190000

10 rows selected.

SQL> 


SY.
Previous Topic: Error while using sql merge
Next Topic: Getting ORA-6502 while using SYS.DBMS_BACKUP_RESTORE.SEARCHFILES(lv_pattern, lv_ns)
Goto Forum:
  


Current Time: Mon Sep 15 22:43:38 CDT 2014

Total time taken to generate the page: 0.87976 seconds