Home » SQL & PL/SQL » SQL & PL/SQL » How do I sum a value? (merged)
How do I sum a value? (merged) [message #234446] Tue, 01 May 2007 11:27 Go to next message
roba
Messages: 11
Registered: December 2006
Junior Member
here is what i have:

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

the issue is i get multiple line for the amsco
2007 2065 pa 12300000 $28
2007 2065 pa 12300000 $64
2007 2065 pa 33300000 $10
2007 2065 pa 33300000 $50

what i need is for it to give follows

2007 2065 pa 12300000 $92
2007 2065 pa 33300000 $60
Re: how do i sum a value [message #234449 is a reply to message #234446] Tue, 01 May 2007 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is the following the result you got from your query?
Quote:
2007 2065 pa 12300000 $28
2007 2065 pa 12300000 $64
2007 2065 pa 33300000 $10
2007 2065 pa 33300000 $50


This is not possible (unless there are blanks but as you didn't use code tags and formatting as requested we can't see it).
You can't multiple lines for the same (appn, fy, pm, amsco) as you group by this.

Moreover your query is syntaxically wrong.
So if you want help post what you REALLY did.
And also post your Oracle version.

Regards
Michel
Re: how do i sum a value [message #234460 is a reply to message #234449] Tue, 01 May 2007 12:22 Go to previous messageGo to next message
roba
Messages: 11
Registered: December 2006
Junior Member
attached is actual file

reselut is this - a few columns

the issue is it does not break disbursments and obligations out by amsco. it runs for pm and puts same ammount in each amsco and i need the ammount for each amsoc.

ORACLE 10G

APPN ACCOUNT AMSCO PMC DISBURSEMENTS OBLIGAITONS
2065 CIF NGP2 11300000 N00 $195,630 $347,899
2065 CIF NGP2 11200000 N00 $195,630 $347,899
2065 CIF NGP2 11400000 N00 $195,630 $347,899
2065 CIF NGP2 11100000 N00 $195,630 $347,899
Re: how do i sum a value [message #234463 is a reply to message #234460] Tue, 01 May 2007 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to build a small test case with only relevant columns and small set of data that raise your issue.
As it, we don't have information enough to answer.
I will tend to say that data are the same ones in each group.

Also, read http://www.orafaq.com/forum/t/59964/102589/ before posting.

Regards
Michel
formula build [message #234476 is a reply to message #234446] Tue, 01 May 2007 14:12 Go to previous messageGo to next message
roba
Messages: 11
Registered: December 2006
Junior Member
i am on ORACLE 10G

help 4hrs and i have no clue how to fix this.

select b.budtitle, b.budpm,b.budfy,c.sag,
sum (case when substr(a.type_trans, 1, 1)='E' and a.eor not like '41%'then amount end),
sum (case when substr(a.type_trans, 1, 1) in ('C','D') and a.eor not like '41%'then amount end)
from stfhistory a,
budgetmaster b,
budgetsagtotal c
where a.pm= b.budpm
and b.budfy='&fy'
and a.fy=b.budfy
and b.budpm='N00'
group by b.budtitle, b.budpm,b.budfy, c.sag

i need each of the sum colums to calculate by each sag. currently it will post the same amount in each sag see below
i am sorry i can not send tables i can not copy them

BUDTITLE BUD BUDF SAG SUM(CASEWHENSUBSTR(A.TYPE_TRANS,1,1)='E'ANDA.EORNOTLIKE'41%'THENAMOUNTEND) SUM(CASEWHENSUBSTR(A.TYPE_TRANS,1,1)IN('C','D')ANDA.EORNOTLIKE'41%'THENAMOUNTEND)
----------- --- ---- --- -------------------------------------------------------------------------- ---------------------------------------------------------------------------------
CIF NGP2 N00 2007 122 782518.48 1391596
CIF NGP2 N00 2007 133 782518.48 1391596
CIF NGP2 N00 2007 434 782518.48 1391596
CIF NGP2 N00 2007 115 782518.48 1391596
CIF NGP2 N00 2007 112 782518.48 1391596

what is should do is calculate by sag
Re: formula build [message #234483 is a reply to message #234476] Tue, 01 May 2007 14:35 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
You'll have to find a way to provide us with some sample data, because otherwise it's nearly impossible to see what is going on...
Re: formula build [message #234485 is a reply to message #234476] Tue, 01 May 2007 14:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
LOL!
You posted the same question as at http://www.orafaq.com/forum/t/80948/102589/ and so got the same answer.

It is useless to repost the same question as long as you don't answer to our questions you won't get more.

In addition, you still don't read How to format your posts or don't care about it. This is not a good way to get an answer.

Regards
Michel

[Updated on: Tue, 01 May 2007 14:56]

Report message to a moderator

Re: formula build [message #234507 is a reply to message #234476] Tue, 01 May 2007 17:47 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
roba wrote on Tue, 01 May 2007 14:12
i am on ORACLE 10G

help 4hrs and i have no clue how to fix this.

select b.budtitle, b.budpm,b.budfy,c.sag,
sum (case when substr(a.type_trans, 1, 1)='E' and a.eor not like '41%'then amount end),
sum (case when substr(a.type_trans, 1, 1) in ('C','D') and a.eor not like '41%'then amount end)
from stfhistory a,
budgetmaster b,
budgetsagtotal c
where a.pm= b.budpm
and b.budfy='&fy'
and a.fy=b.budfy
and b.budpm='N00'
group by b.budtitle, b.budpm,b.budfy, c.sag

i need each of the sum colums to calculate by each sag. currently it will post the same amount in each sag see below
i am sorry i can not send tables i can not copy them

BUDTITLE BUD BUDF SAG SUM(CASEWHENSUBSTR(A.TYPE_TRANS,1,1)='E'ANDA.EORNOTLIKE'41%'THENAMOUNTEND) SUM(CASEWHENSUBSTR(A.TYPE_TRANS,1,1)IN('C','D')ANDA.EORNOTLIKE'41%'THENAMOUNTEND)
----------- --- ---- --- -------------------------------------------------------------------------- ---------------------------------------------------------------------------------
CIF NGP2 N00 2007 122 782518.48 1391596
CIF NGP2 N00 2007 133 782518.48 1391596
CIF NGP2 N00 2007 434 782518.48 1391596
CIF NGP2 N00 2007 115 782518.48 1391596
CIF NGP2 N00 2007 112 782518.48 1391596

what is should do is calculate by sag

You do realize that your sag's are all different right? How is this not doing what you want?
Previous Topic: How create a copy of database
Next Topic: Trouble with FK and PK ref from another table
Goto Forum:
  


Current Time: Wed Dec 07 14:24:08 CST 2016

Total time taken to generate the page: 0.14443 seconds