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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
6065 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  
 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.07 seconds. Powered By: Snitz Forums 2000