Home » SQL & PL/SQL » SQL & PL/SQL » sum multiple column with different criteria
sum multiple column with different criteria [message #345558] Thu, 04 September 2008 03:31 Go to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
hi all
i have table stucture like

symbol     qty     rate     type
------     ---     ----     ----
ABS        4000    11.20      S
HJJ        5000    123.1      B
YWW        3000    23.33      S
GSS        4000    11.20      S
DDD        5000    123.1      B
YWW        3000    23.33      S


note:- In type column "S" represent Sale and "B" represent Buy

i want out put like this
symbol   Net Qty    Net Amount  Buy Qty  Buy Amount  Sell Qty  Sell Amount
-----    -------    ----------  -------  ----------  --------  -----------
ABS        4000       40,000    3000     30,0000      1000     10,000
ABT        4000       40,000    3000     30,0000      1000     10,000
XBS        4000       40,000    3000     30,0000      1000     10,000
VBB        4000       40,000    3000     30,0000      1000     10,000
ASF        4000       40,000    3000     30,0000      1000     10,000
SBS        4000       40,000    3000     30,0000      1000     10,000
RFD        4000       40,000    3000     30,0000      1000     10,000


BUY QTY = SELECT SUM(QTY) FROM INV WHERE TYPE='B'
SELL QTY = SELECT SUM(QTY) FROM INV WHERE TYPE='S'
SELL AMOUNT= SELECT SUM(QTY*RATE) FROM INV WHERE TYPE='S'
BUY AMOUNT= SELECT SUM(QTY*RATE) FROM INV WHERE TYPE='B'



any help is appreciable

[Updated on: Thu, 04 September 2008 03:53]

Report message to a moderator

Re: sum multiple column with different criteria [message #345560 is a reply to message #345558] Thu, 04 September 2008 03:52 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Provide a test case.

Create table and insert statements.

Hint:-Analytical Functions

Regards,
Rajat
Re: sum multiple column with different criteria [message #345562 is a reply to message #345558] Thu, 04 September 2008 03:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're going to have to explain how these extra columns are to be generated.

The standard way to sum columns based on other criteria is to use a construct like
SUM(CASE WHEN test_1 THEN value 1
         WHEN test_2 THEN value 2
         ELSE value 3 END)     
Re: sum multiple column with different criteria [message #345564 is a reply to message #345558] Thu, 04 September 2008 03:58 Go to previous messageGo to next message
jramya
Messages: 42
Registered: April 2008
Member
HI Snomi,

Hope the below helps

SELECT Id,
SUM(qty) Net_qty,
SUM(qty * Amt) Net_Amt,
SUM(DECODE(TYPE,'B',qty)) Buy_qty,
SUM(DECODE(TYPE,'B',qty * Amt)) Buy_Amt,
SUM(DECODE(TYPE,'S',qty)) Sell_qty,
SUM(DECODE(TYPE,'S',qty * Amt)) Sell_Amt
FROM Test1
GROUP BY Id

Regards
Ramya
Re: sum multiple column with different criteria [message #345565 is a reply to message #345558] Thu, 04 September 2008 04:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
snomi wrote on Thu, 04 September 2008 10:31
hi all
i have table stucture like

symbol     qty     rate     type
------     ---     ----     ----
ABS        4000    11.20      S
HJJ        5000    123.1      B
YWW        3000    23.33      S
GSS        4000    11.20      S
DDD        5000    123.1      B
YWW        3000    23.33      S


note:- In type column "S" represent Sale and "B" represent Buy

i want out put like this
symbol   Net Qty    Net Amount  Buy Qty  Buy Amount  Sell Qty  Sell Amount
-----    -------    ----------  -------  ----------  --------  -----------
ABS        4000       40,000    3000     30,0000      1000     10,000
ABT        4000       40,000    3000     30,0000      1000     10,000
XBS        4000       40,000    3000     30,0000      1000     10,000
VBB        4000       40,000    3000     30,0000      1000     10,000
ASF        4000       40,000    3000     30,0000      1000     10,000
SBS        4000       40,000    3000     30,0000      1000     10,000
RFD        4000       40,000    3000     30,0000      1000     10,000


BUY QTY = SELECT SUM(QTY) FROM INV WHERE TYPE='B'
SELL QTY = SELECT SUM(QTY) FROM INV WHERE TYPE='S'
SELL AMOUNT= SELECT SUM(QTY*RATE) FROM INV WHERE TYPE='S'
BUY AMOUNT= SELECT SUM(QTY*RATE) FROM INV WHERE TYPE='B'



any help is appreciable

I don't see a B-entry for ABS.
I see 5000 as B-entry for HJJ and DDD, but no corresponding entry in the output.
Messy example.
Re: sum multiple column with different criteria [message #345572 is a reply to message #345565] Thu, 04 September 2008 04:14 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
THANKS Ramya ... ITS WORKING
Re: sum multiple column with different criteria [message #345580 is a reply to message #345572] Thu, 04 September 2008 04:35 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
One thing more i have database in which i breakup record every year.
Like i breakup data into 2006-2007 and create new namespace for year 2008-2009 i want to fetch data both databases how it is possible
Re: sum multiple column with different criteria [message #345589 is a reply to message #345580] Thu, 04 September 2008 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See answer in the new topic you created for this latter question.

@jramya

Read read OraFAQ Forum Guide
- Format your post
- Don't put solution only hint or clue

Regards
Michel
Re: sum multiple column with different criteria [message #345595 is a reply to message #345564] Thu, 04 September 2008 04:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you two working on the same homework or something?
Re: sum multiple column with different criteria [message #345869 is a reply to message #345595] Fri, 05 September 2008 03:01 Go to previous message
jramya
Messages: 42
Registered: April 2008
Member
Laughing
Can you hear me 'Its not my yesterday Wink'
It was completely based on assumption,I am sure will not work all time.
Its time to re-read Michel references.
Previous Topic: Alternative way to write the query
Next Topic: Different Chassis no against same registration no
Goto Forum:
  


Current Time: Wed Dec 07 22:09:17 CST 2016

Total time taken to generate the page: 0.10379 seconds