getting a sum of a sum

Question

getting a sum of a sum

mswart-971998

Newbie

Points: 7
More actions
April 23, 2010 at 9:49 am

#386777

Hello,
I am having trouble changing this SQL to get my desired results.
I am somewhat new to SQL and this command is one borrowed from an existing report.
The way it is currently written, it sums various cash transactions to get me the total available cash for each unique property ID.
I need to get the total available cash for all property id's.
Any suggests are appreciated.
select
p.property_id,
p.initial_cash+
IsNull((select IsNull(sum(proceeds_am),0)+IsNull(sum(dividends_am),0)
from prop_stk_tran pst (nolock)
where pst.property_id = p.property_id and pst.stk_tran_cd <> 10),0)+
IsNull((select sum(trans_am) from sk_tran skt
inner join safekeeping s on skt.safekeep_id = s.safekeep_id
where s.property_id = p.property_id and trans_am > 0),0)-
IsNull((select sum(cash)+sum(dividends_am)+sum(proceeds_am)+sum(sk_proceeds_am)
from claim_property cp (nolock)
inner join claim c (nolock) on cp.claim_id = c.claim_id
where cp.property_id = p.property_id and c.current_result = 'P'),0)
as Current_Cash
from property p
inner join report r (nolock) on r.report_id=p.report_id
where
(p.initial_cash+
IsNull((select IsNull(sum(proceeds_am),0)+IsNull(sum(dividends_am),0)
from prop_stk_tran pst (nolock)
where pst.property_id = p.property_id and pst.stk_tran_cd <> 10),0)+
IsNull((select sum(trans_am) from sk_tran skt
inner join safekeeping s on skt.safekeep_id = s.safekeep_id
where s.property_id = p.property_id and trans_am > 0),0)-
IsNull((select sum(cash)+sum(dividends_am)+sum(proceeds_am)+sum(sk_proceeds_am)
from claim_property cp (nolock)
inner join claim c (nolock) on cp.claim_id = c.claim_id
where cp.property_id = p.property_id and c.current_result = 'P'),0)) >0

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide table def and (fake) sample data together with your expected result in a ready to use format to show what you're struggling with.

If you need assistance please have a look at the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

mswart-971998 (4/23/2010)
Hello,
I am having trouble changing this SQL to get my desired results.
I am somewhat new to SQL and this command is one borrowed from an existing report.
The way it is currently written, it sums various cash transactions to get me the total available cash for each unique property ID.
I need to get the total available cash for all property id's.
Any suggests are appreciated.
select
p.property_id,
p.initial_cash+
IsNull((select IsNull(sum(proceeds_am),0)+IsNull(sum(dividends_am),0)
from prop_stk_tran pst (nolock)
where pst.property_id = p.property_id and pst.stk_tran_cd <> 10),0)+
IsNull((select sum(trans_am) from sk_tran skt
inner join safekeeping s on skt.safekeep_id = s.safekeep_id
where s.property_id = p.property_id and trans_am > 0),0)-
IsNull((select sum(cash)+sum(dividends_am)+sum(proceeds_am)+sum(sk_proceeds_am)
from claim_property cp (nolock)
inner join claim c (nolock) on cp.claim_id = c.claim_id
where cp.property_id = p.property_id and c.current_result = 'P'),0)
as Current_Cash
from property p
inner join report r (nolock) on r.report_id=p.report_id
where
(p.initial_cash+
IsNull((select IsNull(sum(proceeds_am),0)+IsNull(sum(dividends_am),0)
from prop_stk_tran pst (nolock)
where pst.property_id = p.property_id and pst.stk_tran_cd <> 10),0)+
IsNull((select sum(trans_am) from sk_tran skt
inner join safekeeping s on skt.safekeep_id = s.safekeep_id
where s.property_id = p.property_id and trans_am > 0),0)-
IsNull((select sum(cash)+sum(dividends_am)+sum(proceeds_am)+sum(sk_proceeds_am)
from claim_property cp (nolock)
inner join claim c (nolock) on cp.claim_id = c.claim_id
where cp.property_id = p.property_id and c.current_result = 'P'),0)) >0

If I where you, I'd throw that code away and start over. Even someone new like yourself can write better code than that. Nope... I'm not trying to be a smart guy about this. I'm dead serious. The code is garbage and there's more wrong with it than right. Just throw it away and forget about it. You can do much better on your own. As you do so, remember than you don't have to write an entire solution in a single query.

If you ever run into the person that wrote that, tell them they have to turn in their "Man Card".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)