Home » SQL & PL/SQL » SQL & PL/SQL » sub totals in sql (12c)
sub totals in sql [message #665829] Sun, 24 September 2017 04:45 Go to next message
m.abdulhaq
Messages: 233
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
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 #665831 is a reply to message #665830] Sun, 24 September 2017 07:50 Go to previous messageGo to next message
m.abdulhaq
Messages: 233
Registered: April 2013
Location: Ajman
Senior Member
Excellent Solomon, thanks this is what is want.Is it possible to add a grand total at the end of the rows to check the total by company at end.


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
                          total   70

Re: sub totals in sql [message #665832 is a reply to message #665831] Sun, 24 September 2017 08:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
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.
icon14.gif  Re: sub totals in sql [message #665833 is a reply to message #665832] Sun, 24 September 2017 10:19 Go to previous message
m.abdulhaq
Messages: 233
Registered: April 2013
Location: Ajman
Senior Member
Thanks a lot Solomon.
Previous Topic: Adding column to the table which have the huge data
Next Topic: Multiple record insert using PLSQL
Goto Forum:
  


Current Time: Sat Dec 16 15:22:46 CST 2017

Total time taken to generate the page: 0.01293 seconds