Home » SQL & PL/SQL » SQL & PL/SQL » sub totals in sql (12c)
sub totals in sql [message #665829] |
Sun, 24 September 2017 04:45 |
|
m.abdulhaq
Messages: 254 Registered: April 2013 Location: Ajman
|
Senior Member |
|
|
Hello experts,
I want to write a query without using union to give me subtotals by both comp_cd and comp_acnt and match exact results like below.Is there any other technique.
create table out_inv (comp_cd varchar2(12),comp_acnt varchar2(12),comp_area varchar2(12),inv_amount number)
insert into out_inv(comp_cd,comp_acnt,comp_area,inv_amount) values ('C01','30101','TEX',10)
insert into out_inv(comp_cd,comp_acnt,comp_area,inv_amount) values ('C01','30102','BW',20)
insert into out_inv(comp_cd,comp_acnt,comp_area,inv_amount) values ('C02','30102','TV',20)
insert into out_inv(comp_cd,comp_acnt,comp_area,inv_amount) values ('C01','30201','AR',20)
SELECT comp_cd,comp_acnt,sum(inv_amount) amt
from out_inv
where comp_Acnt in ('30101','30102')
group by rollup (comp_cd,comp_acnt)
union all
SELECT comp_cd,comp_acnt,sum(inv_amount) amt
from out_inv
where comp_Acnt in ('30201')
group by rollup ( comp_cd,comp_acnt);
COMP_CD COMP_ACNT AMT
C01 30101 10.000
C01 30102 20.000
C01 30.000
C02 30102 20.000
C02 20.000
50.000
C01 30201 20.000
C01 20.000
20.000
|
|
|
Re: sub totals in sql [message #665830 is a reply to message #665829] |
Sun, 24 September 2017 07:35 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
SELECT comp_cd,
comp_acnt,
sum(inv_amount) amt
from out_inv
where comp_acnt in ('30101','30102','30201')
group by rollup(
case
when comp_acnt in ('30101','30102') then 1
else 2
end,
comp_cd,
comp_acnt
)
having grouping(
case
when comp_acnt in ('30101','30102') then 1
else 2
end
) = 0
/
COMP_CD COMP_ACNT AMT
------------ ------------ ----------
C01 30101 10
C01 30102 20
C01 30
C02 30102 20
C02 20
50
C01 30201 20
C01 20
20
9 rows selected.
SQL>
SY.
|
|
|
|
Re: sub totals in sql [message #665832 is a reply to message #665831] |
Sun, 24 September 2017 08:53 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Sure, just remove HAVING clause:
SELECT comp_cd,
comp_acnt,
sum(inv_amount) amt
from out_inv
where comp_acnt in ('30101','30102','30201')
group by rollup(
case
when comp_acnt in ('30101','30102') then 1
else 2
end,
comp_cd,
comp_acnt
)
COMP_CD COMP_ACNT AMT
------------ ------------ ----------
C01 30101 10
C01 30102 20
C01 30
C02 30102 20
C02 20
50
C01 30201 20
C01 20
20
70
10 rows selected.
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:38:25 CDT 2024
|