Home » SQL & PL/SQL » SQL & PL/SQL » How to get total of All the columns
How to get total of All the columns [message #207247] 
Mon, 04 December 2006 19:03 
shyamjha
Messages: 11 Registered: September 2006 Location: Mumbai

Junior Member 


Hi
Can any one help me in geting sum of all the cloumns
as last column in the below query:
SELECT t.month_no,
NVL(sum(Decode(t.ITEM_CATEGORY,'CBB',t.TOTAL_SPENT)),0) CBB,
NVL(sum(Decode(t.ITEM_CATEGORY,'CON',t.TOTAL_SPENT)),0) CON,
NVL(sum(Decode(t.ITEM_CATEGORY,'FLR',t.TOTAL_SPENT)),0) FLR,
NVL(sum(Decode(t.ITEM_CATEGORY,'FME',t.TOTAL_SPENT)),0) FME,
NVL(sum(Decode(t.ITEM_CATEGORY,'MDD',t.TOTAL_SPENT)),0) MDD,
NVL(sum(Decode(t.ITEM_CATEGORY,'MIS',t.TOTAL_SPENT)),0) MIS,
NVL(sum(Decode(t.ITEM_CATEGORY,'MRO',t.TOTAL_SPENT)),0) MRO,
NVL(sum(Decode(t.ITEM_CATEGORY,'PLD',t.TOTAL_SPENT)),0) PLD,
NVL(sum(Decode(t.ITEM_CATEGORY,'SRV',t.TOTAL_SPENT)),0) SRV,
NVL(sum(Decode(t.ITEM_CATEGORY,'SRV',t.TOTAL_SPENT)),0) SRV,
NVL(sum(Decode(t.ITEM_CATEGORY,'SSO',t.TOTAL_SPENT)),0) SSO,
NVL(sum(Decode(t.ITEM_CATEGORY,'SUP',t.TOTAL_SPENT)),0) SUP
FROM
( SELECT TO_CHAR(TO_DATE(RECEIPT_DATE,'DD/MM/YYYY'),'MM') MONTH_NO,
ITEM_CATEGORY, SUM(TOTAL_SPEND) TOTAL_SPENT
FROM spent
GROUP BY ITEM_CATEGORY, TO_CHAR(TO_DATE(RECEIPT_DATE,'DD/MM/YYYY'),'MM')
ORDER BY 1
) t
GROUP BY ROLLUP(t.month_no);
Thanks in advance.
Shyam




Re: How to get total of All the columns [message #207471 is a reply to message #207247] 
Tue, 05 December 2006 12:35 
shyamjha
Messages: 11 Registered: September 2006 Location: Mumbai

Junior Member 


Hi Ross Leishman,
Thanks for your reply!!
I am sorry for not giving the proper details.
Actually I want only the sum of all those columns dispalyed in my first query i.e CBB, CON, FLR, FME, MDD, MIS, MRO, PAC, PLD,SRV, SSO, SUP
where as my subqueery is returing the more then that where I do not want to restrict like (XXX, BVR).
If I will put sum(Total_spent) its adding all the values which are not present in the first query i.e. it includes (XXX, BVR).
Can you please help me doing so.
Thanks,
Shyam



Goto Forum:
Current Time: Sat Apr 29 16:21:54 CDT 2017
Total time taken to generate the page: 0.13813 seconds
