Home » SQL & PL/SQL » SQL & PL/SQL » how can we get multiple SUM() from single table
how can we get multiple SUM() from single table [message #243920] Mon, 11 June 2007 01:05 Go to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
For example if tab_emp is the table i want like the below format

select sum(sal)-->from tab_emp where dept='software' andage>'30',
sum(sal) --> from tab_emp where dept='marketing' and age>'30'
sum(sal) --> from tab_emp where dept='accounts' and age >'30'
Re: how can we get multiple SUM() from single table [message #243927 is a reply to message #243920] Mon, 11 June 2007 01:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
So you want to GROUP the sum BY department? Oracle has a construction for that:
SELECT SUM(sal) sal
     , dept
FROM   emp_tab
WHERE  age > 30
AND    dept IN ( 'software', 'marketing', 'accounts')
GROUP BY dept


Play around with GROUP BY.

MHE
Re: how can we get multiple SUM() from single table [message #243928 is a reply to message #243920] Mon, 11 June 2007 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please always post your Oracle version (4 decimals).
Please read and apply How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS.

First age is a number, '30' is a string so an age of 100 is less than '30'. Fix it.
Then you can use decode, search here or on AskTom for "pivot", you'll find many examples.

Regards
Michel

Re: how can we get multiple SUM() from single table [message #243934 is a reply to message #243927] Mon, 11 June 2007 01:22 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
thx for the reply,

No i want individual SUM'S that is

sum(software),sum(marketing),sum(accounts)

that means the output look like this;

software marketing accounts
--------- ----------- ---------
120000.00 400000.00 405000.00

thx & Rgds
Ajay
Re: how can we get multiple SUM() from single table [message #243941 is a reply to message #243934] Mon, 11 June 2007 01:31 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Be clear. Read Michel's reply and act to it. Format your required output.

Now, as for your question. I used the standard emp and dept table. To make the example as easy as possible, I've just selected the sum of the salary for each department.

SQL> SELECT SUM(DECODE(e.deptno, 10, e.sal, 0)) SAL_ACCOUNTING
  2       , SUM(DECODE(e.deptno, 20, e.sal, 0)) SAL_RESEARCH
  3       , SUM(DECODE(e.deptno, 30, e.sal, 0)) SAL_SALES
  4       , SUM(DECODE(e.deptno, 40, e.sal, 0)) SAL_OPERATIONS
  5  FROM   emp e
  6  /

SAL_ACCOUNTING SAL_RESEARCH  SAL_SALES SAL_OPERATIONS
-------------- ------------ ---------- --------------
          8750        10875       9400              0



MHE
Re: how can we get multiple SUM() from single table [message #243946 is a reply to message #243941] Mon, 11 June 2007 01:44 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi Maaher

but i hav to use three conditions, I wil give u my exact query plz dont mine...

i hav forwarded my query as a jpeg plz go through it

Thx

  • Attachment: query.JPG
    (Size: 31.90KB, Downloaded 91 times)
Re: how can we get multiple SUM() from single table [message #243950 is a reply to message #243946] Mon, 11 June 2007 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use JPEG many of us can't download it.
Copy and paste it with formatting.

You got an answer for the question you posted.
Next time, post the real question then you'll get an answer for your problem and not another one.

Regards
Michel
Re: how can we get multiple SUM() from single table [message #243953 is a reply to message #243950] Mon, 11 June 2007 01:57 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
------------------------------------------------------------------------------------------------------------------
Field | Type & Length | Table | DB Field | Condition
------------------------------------------------------------------------------------------------------------------
Cash In Flow (actual) | char(18) | ifschd | sum(txamt) | type = 'A' and crdb = '2'
Cash Out Flow (actual) | char(18) | ifschd | sum(txamt) | type = 'A' and crdb = '1'
Cash In Flow (Forecasted) | char(18) | ifschd | sum(txamt) | type = 'S' and crdb = '2'
Cash Out Flow (Forecasted) | char(18) | ifschd | sum(txamt) | type = 'S' and crdb = '1'
------------------------------------------------------------------------------------------------------------------

my requirement will be as abouve
Re: how can we get multiple SUM() from single table [message #243959 is a reply to message #243953] Mon, 11 June 2007 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't you see the first link I posted? Do you read it?
Format your post!

Regards
Michel
Re: how can we get multiple SUM() from single table [message #243964 is a reply to message #243959] Mon, 11 June 2007 02:13 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
------------------------------------------------------------------------------------------------------------------
Field | Type & Length | Table | DB Field | Condition
------------------------------------------------------------------------------------------------------------------
Cash In Flow (actual) | char(18) | ifschd | sum(txamt) | type = 'A' and crdb = '2'
Cash Out Flow (actual) | char(18) | ifschd | sum(txamt) | type = 'A' and crdb = '1'
Cash In Flow (Forecasted) | char(18) | ifschd | sum(txamt) | type = 'S' and crdb = '2'
Cash Out Flow (Forecasted) | char(18) | ifschd | sum(txamt) | type = 'S' and crdb = '1'
------------------------------------------------------------------------------------------------------------------
Re: how can we get multiple SUM() from single table [message #243966 is a reply to message #243959] Mon, 11 June 2007 02:16 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
------------------------------------------------------------------------------------------------------------------
Field | Type & Length | Table | DB Field | Condition
------------------------------------------------------------------------------------------------------------------
Cash In Flow (actual) | char(18) | ifschd | sum(txamt) | type = 'A' and crdb = '2'
Cash Out Flow (actual) | char(18) | ifschd | sum(txamt) | type = 'A' and crdb = '1'
Cash In Flow (Forecasted) | char(18) | ifschd | sum(txamt) | type = 'S' and crdb = '2'
Cash Out Flow (Forecasted) | char(18) | ifschd | sum(txamt) | type = 'S' and crdb = '1'
------------------------------------------------------------------------------------------------------------------

i hav given top as the table header i.e. Field name , type&length,Table name, DBField Name and Condition which i want
plz help me.
Re: how can we get multiple SUM() from single table [message #243967 is a reply to message #243964] Mon, 11 June 2007 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Edit your post (click on the Edit button), click on the last button just before the "Size" field and put your text between the 2 CODE tags.

Regards
Michel
Re: how can we get multiple SUM() from single table [message #243976 is a reply to message #243967] Mon, 11 June 2007 02:42 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
----------------------------------------------------------------------------------------------
Field                      | Type & Length  | Table  | DB Field   | Condition
----------------------------------------------------------------------------------------------
Cash In Flow (actual)      | char(18)       | ifschd | sum(txamt) | type = 'A' and crdb = '2'
Cash Out Flow (actual)     | char(18)       | ifschd | sum(txamt) | type = 'A' and crdb = '1'
Cash In Flow (Forecasted)  | char(18)       | ifschd | sum(txamt) | type = 'S' and crdb = '2'
Cash Out Flow (Forecasted) | char(18)       | ifschd | sum(txamt) | type = 'S' and crdb = '1'
----------------------------------------------------------------------------------------------


The below above is the requirement of my table , The top most i hav given description abut my table IFSCHD, txamt---> field name

[Updated on: Mon, 11 June 2007 02:55] by Moderator

Report message to a moderator

Re: how can we get multiple SUM() from single table [message #243981 is a reply to message #243976] Mon, 11 June 2007 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take Maarten's query, change "decode" to "case" to express your condition and this is done.

Regards
Michel
Re: how can we get multiple SUM() from single table [message #243984 is a reply to message #243981] Mon, 11 June 2007 03:03 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hello,

i have tried what u hav given to me it is working for single condition but if i use like this as below it is not working,


SELECT SUM(DECODE(f.type, 'A', e.txamt, 0) AND (f.crdb,'2',e.txamt,0)) ACT_CASH_INFLOW
, SUM(DECODE(f.type, 'A', e.txamt, 0) AND (f.crdb,'1',e.txamt,0)) ACT_CASH_OUTFLOW
, SUM(DECODE(f.type, 'S', e.txamt, 0) AND (f.crdb,'2',e.txamt,0)) FORCST_CASH_INFLOW
, SUM(DECODE(f.type, 'A', e.txamt, 0) AND (f.crdb,'1',e.txamt,0)) FORCST_CASH_OUTFLOW
FROM ifschd f;

i want to check wheather it is satisfying two conditions?
thx & rgds
Ajay,
Re: how can we get multiple SUM() from single table [message #243985 is a reply to message #243984] Mon, 11 June 2007 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CASE

Regards
Michel
Re: how can we get multiple SUM() from single table [message #243987 is a reply to message #243985] Mon, 11 June 2007 03:13 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hi Michel
i am very new to PL/SQL can u plz show me the query using CASE.

thx & rgds
Ajay

[Updated on: Mon, 11 June 2007 03:15]

Report message to a moderator

Re: how can we get multiple SUM() from single table [message #243988 is a reply to message #243987] Mon, 11 June 2007 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are new, you need to learn.
The best way to learn is try, try, try.
Here's the page of SQL Reference, CASE.
There are examples.
Try and if you are stuck, post what you tried we will help you.
If you find the answer, post it also, this will help new comers.

Regards
Michel
Re: how can we get multiple SUM() from single table [message #243989 is a reply to message #243988] Mon, 11 June 2007 03:41 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
ok thanks a lot i wil try as u said.... if i find the answer i wil definately post to u Cool
Re: how can we get multiple SUM() from single table [message #243995 is a reply to message #243988] Mon, 11 June 2007 04:17 Go to previous messageGo to next message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
hello Michel & Maaher,

i am very happy to say u that i got solution. U both r very great. U r my success thx a lotttttttt.
the solution is


SELECT SUM (CASE
               WHEN e.TYPE = 'A' OR e.crdb = '2'
                  THEN e.txamt
            END) "CASH IN FLOW (ACTUAL)"
     , SUM (CASE
               WHEN e.TYPE = 'A' OR e.crdb = '1'
                  THEN e.txamt
            END) "CASH OUT FLOW (ACTUAL)"
     , SUM (CASE
               WHEN e.TYPE = 'S' OR e.crdb = '2'
                  THEN e.txamt
            END) "CASH IN FLOW (FORECASTED)"
     , SUM (CASE
               WHEN e.crdb = 'S' OR e.crdb = '1'
                  THEN e.txamt
            END) "CASH OUT FLOW (FORECASTED)"
FROM   ifschd e;


special thanks to Michel who gave some good example made me to try my self.

Thx & Rgds
Ajay

[Updated on: Mon, 11 June 2007 04:20] by Moderator

Report message to a moderator

Re: how can we get multiple SUM() from single table [message #243997 is a reply to message #243995] Mon, 11 June 2007 04:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You're welcome. Isn't it nice? You got some help but figured it out yourself. I wish more people would do that...

MHE
Re: how can we get multiple SUM() from single table [message #244000 is a reply to message #243995] Mon, 11 June 2007 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I completly agree with Maarten.

Regards
Michel
Re: how can we get multiple SUM() from single table [message #244022 is a reply to message #243995] Mon, 11 June 2007 05:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
msg2ajay wrote on Mon, 11 June 2007 11:17

WHEN e.TYPE = 'A' OR e.crdb = '2'


Don't you mean AND instead of OR??
Re: how can we get multiple SUM() from single table [message #244023 is a reply to message #244022] Mon, 11 June 2007 06:06 Go to previous message
msg2ajay
Messages: 51
Registered: June 2007
Location: KUALA LUMPUR
Member
oooo great,

as up to 'AND' i dont have data in my table so i placed 'OR' insted of 'AND' for testing, so i have pasted my query as it is.

thx once more,
Ajay
Previous Topic: Case and Decode
Next Topic: SQL Loader / Temp table
Goto Forum:
  


Current Time: Tue Dec 06 06:28:29 CST 2016

Total time taken to generate the page: 0.30066 seconds