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 Go to next message
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 #207253 is a reply to message #207247] Mon, 04 December 2006 19:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Trick question? What's wrong with SUM(total_spent)?

Ross Leishman
Re: How to get total of All the columns [message #207471 is a reply to message #207247] Tue, 05 December 2006 12:35 Go to previous message
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
Previous Topic: Case insensitive query (merged)
Next Topic: type_name for %rowtype
Goto Forum:
  


Current Time: Sun Dec 11 08:05:27 CST 2016

Total time taken to generate the page: 0.09094 seconds