CODEWITHSUNDEEP
sqloracle-database
mrdiu
mrdiu

Reputation: 49

How do I use grouping set to calculate grand total based on column value?

Maybe grouping set is not what I need to use, but it works to give me a grand total of everything. What I need is a grand total based on the column comp_code value. I'm just not sure how to go about? I've tried cubing and rollup with no success, just makes the table look real messy.

Any help would be greatly appreciated. Thanks in advance for the help and your time.

Current SQL

select nvl(a.comp_code, 'GRAND TOTAL) as COMP_CODE, a.inv_prex, a.inv_num, to_char(a.inv_date,'yy-mm-dd') as inv_date, a.cust_code, b.cust_name, sum(round(a.inv_amt,2)) as inv_amt, a.inv_reg_num
from c_inv_h a 
left join m_cust_h_b
on a.comp_code=b.comp_code and a.cust_code=b.cust_code
where a.inv_date between to_date('20\06\01', 'yy\mm\dd') and to_date('20\06\24', 'yy\mm\dd')
and a.cust_code='102204'
group by
grouping sets
((),
(a.inv_prex, a.inv_num, a.inv_date, a.comp_code, a.cust_code, b.cust_name, a.inv_reg_num))

Current Output

comp_code | inv_prex | inv_num | inv_date | cust_code | cust_name   | inv_amt | inv_reg_num
---------------------------------------------------------------------------------------------
C4           AIC        410       20-06-01   102204      test comp     157.5    302
C4           AIC        432       20-06-05   102204      test comp     63       301
T3           AIT        1272      20-06-01   102204      test comp     1134.97  335
GRAND TOTAL                                                            1355.47

Output I'm trying to achieve

comp_code | inv_prex | inv_num | inv_date | cust_code | cust_name   | inv_amt | inv_reg_num
---------------------------------------------------------------------------------------------
C4           AIC        410       20-06-01   102204      test comp     157.5    302
C4           AIC        432       20-06-05   102204      test comp     63       301
GRAND TOTAL                                                            220.5
T3           AIT        1272      20-06-01   102204      test comp     1134.97  335
GRAND TOTAL                                                            1134.97

Upvotes: 0

Views: 348

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I think you want:

group by grouping sets ((a.inv_prex, a.inv_num),
                        (a.inv_prex, a.inv_num, a.inv_date, a.comp_code, a.cust_code, b.cust_name, a.inv_reg_num)
                       )

Upvotes: 2

Related Questions