OraFAQ Forum: SQL & PL/SQL » How to get total of All the columns

Home » SQL & PL/SQL » SQL & PL/SQL » How to get total of All the columns

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: How to get total of All the columns [message #207253 is a reply to message #207247]

Mon, 04 December 2006 19:43

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Trick question? What's wrong with SUM(total_spent)?

Ross Leishman

Report message to a moderator

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

Report message to a moderator

Previous Topic:	Case insensitive query (merged)
Next Topic:	type_name for %rowtype

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 09:04:56 CST 2024