Home » SQL & PL/SQL » SQL & PL/SQL » sum function on two colums but same table
sum function on two colums but same table [message #207188] Mon, 04 December 2006 09:29 Go to next message
roba
Messages: 11
Registered: December 2006
Junior Member
new to this been working on this for 6 hrs and can not seam to Google or find my way to an answer. I need to have two columns one for disbursement query 1 ("E%") and one for obligations query 2 ('C%' 'D%')that come from the the same table. Both query work but i need to get the first one to run the 2nd query. when i write in the 2nd all id does is sum for e,c,d not break it out between e, and (c,d)

1st query


select b.budappn, b.budtitle, b.budmamsco, b.budpm, b.budmdep,b.budafp,
b.budallot, b.budfy,
b.budorigoblg10, b.budorigoblg11, b.budorigoblg12, b.budorigoblg01,
b.budorigoblg02, b.budorigoblg03, b.budorigoblg04, b.budorigoblg05,
b.budorigoblg06, b.budorigoblg07,
b.budorigoblg08, b.budorigoblg09, b.budstpmname, a.pm,sum (a.amount)
from stfhistory a,
budgetmaster b
where a.pm= b.budpm
and b.budfy='2007'
and a.fy='2007'
and b.budappn='2065'
and a.type_trans like 'E%'
and a.eor not like '41%'
group by b.budappn, b.budtitle, b.budmamsco, b.budpm, b.budmdep, b.budafp,
b.budallot, b.budfy, b.budorigoblg10, b.budorigoblg11, b.budorigoblg12,
b.budorigoblg01, b.budorigoblg02, b.budorigoblg03, b.budorigoblg04,
b.budorigoblg05, b.budorigoblg06, b.budorigoblg07,b.budorigoblg08,
b.budorigoblg09, b.budstpmname,a.pm
/


2nd quarey

select fy,appn,pm, sum (amount) from stfhistory
where fy='2007'
and type_trans like 'C%'
or type_trans like 'D%'
and eor not like '41%'
group by fy,appn,pm
/

[Updated on: Mon, 04 December 2006 09:32]

Report message to a moderator

Re: sum function on two colums but same table [message #207191 is a reply to message #207188] Mon, 04 December 2006 09:52 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Is there a relationship between the rows returned by the first query and the second. In otherwords, how can they be joined.
Re: sum function on two colums but same table [message #207193 is a reply to message #207191] Mon, 04 December 2006 09:58 Go to previous messageGo to next message
roba
Messages: 11
Registered: December 2006
Junior Member
both are looking at the ammount colum from stfhistory.
i just want to show in two columns not one. right know i get all the requested date with the ammount coloum giviving me the sum of 'e' type transactions with a.oer not '41%'. I need another column in query 1 that give me the sum ammount for 'c' or 'd' witheor not like '41%'. when i type in lines 3 and 4 from 2nd query it gives me one colum with sum of e,c,d with eor not like '41%', it does not break into to colums like i need

[Updated on: Mon, 04 December 2006 10:11]

Report message to a moderator

Re: sum function on two columns but same table [message #207204 is a reply to message #207191] Mon, 04 December 2006 10:27 Go to previous messageGo to next message
roba
Messages: 11
Registered: December 2006
Junior Member
both are looking at the amount column from stfhistory.
i just want to show in two columns not one. right know i get all the requested data with the amount column giving me the sum of 'e' type transactions with a.oer not '41%'. I need another column in query 1 that give me the sum amount for 'c' or 'd' witheor not like '41%'. when i type in lines 3 and 4 from 2nd query it gives me one column with sum of e,c,d with eor not like '41%', it does not break into two columns like i need
[Updated on: Mon, 04 December 2006 10:11]
(what i want is to have one query that does both functions for amount, but in two separate columns)

[Updated on: Mon, 04 December 2006 10:33]

Report message to a moderator

Re: sum function on two columns but same table [message #207216 is a reply to message #207204] Mon, 04 December 2006 12:39 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Roba,

It would be easier to help you find a solution if you provide the create table scripts and some sample data. And preferably, simplify the queries just to show the problem.
Re: sum function on two columns but same table [message #207218 is a reply to message #207216] Mon, 04 December 2006 13:04 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Also,

Include desired results
Re: sum function on two columns but same table [message #207219 is a reply to message #207216] Mon, 04 December 2006 13:07 Go to previous messageGo to next message
roba
Messages: 11
Registered: December 2006
Junior Member
query on disbursements yields this

select fy,appn,pm, sum (amount)from stfhistory
where fy='2007'
and type_trans like 'E%'
and eor not like '41%'
group by fy,appn,pm
/

FY APPN PM SUM(AMOUNT)
---- ---- --- -----------
2007 0100 AX0 65.50
2007 0100 ES0 309.49

query on obligations get this

select fy,appn,pm, sum (amount) from stfhistory
where fy='2007'
and type_trans like 'C%'
or type_trans like 'D%'
and eor not like '41%'
group by fy,appn,pm
/


FY APPN PM SUM(AMOUNT)
2007 0100 AX0 321.00
2007 0100 ES0 309.49

what i need is this

FY APPN PM SUM(AMOUNT) SUM (AMOUNT)
2007 0100 AX0 99999 99999

[Updated on: Tue, 05 December 2006 06:51]

Report message to a moderator

Re: sum function on two columns but same table [message #207221 is a reply to message #207219] Mon, 04 December 2006 13:20 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Your two queries are identical. How do they yield different results?
Re: sum function on two columns but same table [message #207399 is a reply to message #207221] Tue, 05 December 2006 06:53 Go to previous messageGo to next message
roba
Messages: 11
Registered: December 2006
Junior Member
sorry, see updated message with correct queries
Re: sum function on two columns but same table [message #207405 is a reply to message #207399] Tue, 05 December 2006 07:25 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
select a.fy,a.appn,a.pm,a.sum (amount),b.sum(amount) from stfhistory a,sfthistory b
where fy='2007' and a.eor not like '41%'
and a.type_trans like 'E%'
or b.type_trans like 'C%'
or b.type_trans like 'D%'
group by a.fy,a.appn,a.pm
/




WARNING:untested

just use these variations and u will end up with that you need
Re: sum function on two columns but same table [message #207416 is a reply to message #207405] Tue, 05 December 2006 07:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
select fy
,      appn
,      pm
,      sum (case when substr(type_trans, 1, 1) = 'E' then amount else 0 end) 
,      sum (case when substr(type_trans, 1, 1) in ('C', 'D') then amount else 0 end)
from   stfhistory
where  fy = '2007'
and    (  type_trans like 'E%'
       or type_trans like 'D%'
       or type_trans like 'C%'
       )
and    eor not like '41%'
group  by fy
,      appn
,      pm


Also, not tested, since I don't have the table-layout or testdata
icon14.gif  Re: sum function on two columns but same table [message #207435 is a reply to message #207416] Tue, 05 December 2006 09:51 Go to previous messageGo to next message
roba
Messages: 11
Registered: December 2006
Junior Member
Fantastic

I have about 9 hours in this now. Worked great thanks for the help. Now i just need to bring it into a bigger query i am working on. For my education what does substr stand for? and the use of the 1, 1 i assume is to look at several qualifiers or am i wrong.

The only change i did was to take off the else 0 end. what is that piece telling the quarry?
Re: sum function on two columns but same table [message #207440 is a reply to message #207435] Tue, 05 December 2006 10:10 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
substr to get the sub string.
substr(type_trans, 1, 1) is getting the first character.

"else 0" is not that useful in this scenario.

sum (case when substr(type_trans, 1, 1) = 'E' then amount else 0 end) means if the fist character of the type_trans it will get the amount to sum it up other wise it will get 0.

IF you don't mention the else part it will get NULL.
As sum function only consider the not null values, if you remove it also you will get same results in this scenario.

By
Vamsi
Previous Topic: Output from group function (merged)
Next Topic: Performance issue with indexing
Goto Forum:
  


Current Time: Fri Dec 09 23:08:59 CST 2016

Total time taken to generate the page: 0.08768 seconds