SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 sub select inside a sum function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Teachme
Starting Member

45 Posts

Posted - 11/20/2006 :  15:14:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5948 Posts

Posted - 11/20/2006 :  15:46:01  Show Profile  Reply with Quote
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 - 11/20/2006 :  16:23:15  Show Profile  Reply with Quote
thanks for ur help..it works
Go to Top of Page

Teachme
Starting Member

45 Posts

Posted - 11/20/2006 :  16:23:19  Show Profile  Reply with Quote
thanks for ur help..it works
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000