Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 sub select inside a sum function

Author  Topic 

Teachme
Starting Member

45 Posts

Posted - 2006-11-20 : 15:14:49
i have a following query where i'm using a sub select and then summing a column but its not letting me do that becuase sum cannot be applied on a sub select. But when i try to put sum insdide such as sum(cost_ps) then it asks me to put transaction_type_cd in the group by. Is there a way to sum the column while using the sub select and i dont have to put the transaction code in the group by because i dont need to group by the code it gives me wrong results. any kind of suggestion would be appreciated. thanks

select vt.account_sub
,vt.cusip_id
,sum(case
when vt.transaction_type_cd in
(select transaction_type_cd
from tbl_maint_transactions_trans_trans_type_cd
where test='unrealized_gain1_sch3a')
then vt.cost_ps
when vt.transaction_type_cd in
(select transaction_type_cd
from tbl_maint_transactions_trans_trans_type_cd
where test='unrealized_gain1_sch3a') and vt.cost_ps > 0
then vt.cost_ps end) as unrealized_gain

from vw_perac_transaction as vt

group by
,vt.account_sub
,vt.cusip_id

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-20 : 15:46:01
I think this will put your existing logic in a form that can execute without errors:

select vt.account_sub
,vt.cusip_id
,sum(case
when tc.test='unrealized_gain1_sch3a' then vt.cost_ps
when tc.test='unrealized_gain1_sch3a' and vt.cost_ps > 0 then vt.cost_ps
end) as unrealized_gain
from vw_perac_transaction as vt
left outer join tbl_maint_transactions_trans_trans_type_cd tc
on tc.transaction_type_cd = vt.transaction_type_cd
group by vt.account_sub
,vt.cusip_id


However, it looks like the logic in your case statements is funky, this slightly simplified statement should return the same results:

select vt.account_sub
,vt.cusip_id
,sum(case when tc.test='unrealized_gain1_sch3a' then vt.cost_ps end) as unrealized_gain
from vw_perac_transaction as vt
left outer join tbl_maint_transactions_trans_trans_type_cd tc
on tc.transaction_type_cd = vt.transaction_type_cd
group by vt.account_sub
,vt.cusip_id


Be One with the Optimizer
TG
Go to Top of Page

Teachme
Starting Member

45 Posts

Posted - 2006-11-20 : 16:23:15
thanks for ur help..it works
Go to Top of Page

Teachme
Starting Member

45 Posts

Posted - 2006-11-20 : 16:23:19
thanks for ur help..it works
Go to Top of Page
   

- Advertisement -