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
 Transact-SQL (2000)
 Best way to solve this?

Author  Topic 

Antonio
Posting Yak Master

168 Posts
Posted - 2007-05-21 : 08:47:34
Greetings and salutations!

I have the following line of code in my select statement :


count(case when event_type in (select event_type from sports_type_lookup where hdo = 'Football') then betid end)


but it doesn't work. Why can't I have a select statement in my aggregate function and what is the best way around this?

Thanks for your help in advance.


_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-05-21 : 08:50:11
can you post the full query ? Without seeing the full picture, it is kind of hard to tell what you want here.

KH
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2007-05-21 : 08:51:04
sum(case when l.event_type is not null then 1 else 0 end)
from tbl t
left join sports_type_lookup l
on l.event_type = t.event_type
and l.hdo = 'Football'


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-05-21 : 08:51:48
Best way would be to convert this IN condition to JOIN.

Select count(t1.betid)
from Table1 t1 join sports_type_lookup t2
on t1.event_type = t2.event_type
Where t2.hdo = 'Football'


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

Antonio
Posting Yak Master

168 Posts
Posted - 2007-05-21 : 08:56:23
Hi guys,

Thanks for the quick respsonses. I did think of doing the join but maye if I give you the entire query then it would make more sense why I didn't carry on with this idea before asking your guys for your expert opinion ;)


declare @V_Date datetime 
set @V_date = '2007.05.13'

-- select distinct hdo
-- from sports_type_lookup


select
	cliaccid,
	ACCOUNTNUM,
	count(case when event_type in (select event_type from sports_type_lookup where hdo = 'Football') then betid end) as Number_Of_Football_Bets_Over_Lifetime, 

	convert(numeric(10, 2), sum(case when event_type in (select event_type from sports_type_lookup where hdo = 'Football') then stake else 0 end) 
	/ nullif(count(case when event_type in (select event_type from sports_type_lookup where hdo = 'Football') then betid end), 0)) as AVG_Stake_Per_Football_Bet_Over_lifetime,

	sum(case when event_type in (select event_type from sports_type_lookup where hdo = 'Football') then profit else 0 end) as GW_of_Football_Bets_Over_Lifetime,
	count(case when event_type in (select event_type from sports_type_lookup where hdo in ('International Horse', 'UK Horse')) then betid end) as Number_Of_Horse_Racing_Bets_Over_Lifetime, 
	
	convert(numeric(10, 2), sum(case when event_type  in (select event_type from sports_type_lookup where hdo in ('International Horse', 'UK Horse')) then stake else 0 end ) 
	/ nullif(count(case when event_type  in (select event_type from sports_type_lookup where hdo in ('International Horse', 'UK Horse')) then betid  end), 0)) as AVG_Stake_Per_Horse_Racing_Bet_Over_Lifetime,
	sum(case when event_type  in (select event_type from sports_type_lookup where hdo in ('International Horse', 'UK Horse')) then profit else 0 end ) as GW_of_Horce_Racing_Bets_Over_Lifetime,

	count(case when event_type in (select event_type from sports_type_lookup where hdo = 'Greyhounds') then betid end) as Number_Of_Greyhounds_Bets_Over_lifetime, 	
	convert(numeric(10, 2), sum(case when event_type in (select event_type from sports_type_lookup where hdo = 'Greyhounds') then stake else 0 end ) 
	/ nullif(count(case when event_type in (select event_type from sports_type_lookup where hdo = 'Greyhounds') then betid end), 0)) as AVG_Stake_Per_Greyhounds_Bet_Over_Lifetime,
	sum(case when event_type in (select event_type from sports_type_lookup where hdo = 'Greyhounds') then profit else 0 end ) as GW_of_Greyhounds_Bets_Over_Lifetime,
	
	count(case when event_type in (select event_type from sports_type_lookup where hdo = 'Other') then betid end) as Number_of_Other_Sports_Bets_Over_Lifetie,

	convert(numeric(10, 2), sum(case when event_type in (select event_type from sports_type_lookup where hdo = 'Other') then stake else 0 end) 
	/ nullif(count(case when event_type  in (select event_type from sports_type_lookup where hdo = 'Other') then betid end), 0)) as AVG_Stake_Per_Other_Sports_Bet_Over_Lifetime,
	sum(case when event_type  in (select event_type from sports_type_lookup where hdo = 'Other') then profit else 0 end ) as GW_of_Other_Sports_Bets_Over_Lifetime,
	
	cast(null as int) as Number_Of_Football_Bets_52_Weeks,
	cast(null as int) as Number_Of_Horse_Racing_Bets_52_Weeks,
	cast(null as int) as Number_Of_Greyhounds_Bets_52_Weeks,
	cast(null as int) as Number_of_Other_Sports_Bets_52_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Football_Bet_52_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Horse_Racing_Bet_52_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Greyhounds_Bet_52_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Other_Sports_Bet_52_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Football_Bets_52_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Horce_Racing_Bets_52_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Greyhounds_Bets_52_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Other_Sports_Bets_52_Weeks,

	cast(null as int) as Number_Of_Football_Bets_12_Weeks,
	cast(null as int) as Number_Of_Horse_Racing_Bets_12_Weeks,
	cast(null as int) as Number_Of_Greyhounds_Bets_12_Weeks,
	cast(null as int) as Number_of_Other_Sports_Bets_12_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Football_Bet_12_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Horse_Racing_Bet_12_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Greyhounds_Bet_12_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Other_Sports_Bet_12_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Football_Bets_12_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Horce_Racing_Bets_12_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Greyhounds_Bets_12_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Other_Sports_Bets_12_Weeks,

	cast(null as int) as Number_Of_Football_Bets_4_Weeks,
	cast(null as int) as Number_Of_Horse_Racing_Bets_4_Weeks,
	cast(null as int) as Number_Of_Greyhounds_Bets_4_Weeks,
	cast(null as int) as Number_of_Other_Sports_Bets_4_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Football_Bet_4_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Horse_Racing_Bet_4_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Greyhounds_Bet_4_Weeks,
	cast(null as numeric(10, 2)) as AVG_Stake_Per_Other_Sports_Bet_4_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Football_Bets_4_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Horce_Racing_Bets_4_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Greyhounds_Bets_4_Weeks,
	cast(null as numeric(10, 2)) as GW_of_Other_Sports_Bets_4_Weeks into dbo.CRM_SPORT_BY_CUSTOMER
from
	dt_telebetmis
group by
	cliaccid, 
	ACCOUNTNUM


_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2007-05-21 : 10:39:45
the join would be a lot more efficient

count(case when l.hdo = 'Football' then betid end) as Number_Of_Football_Bets_Over_Lifetime,
sum(case when hdo = 'Football' then profit else 0 end) as GW_of_Football_Bets_Over_Lifetime,

...

from
dt_telebetmis t
left join sports_type_lookup l
on l.event_type = t.event_type
group by
cliaccid,
ACCOUNTNUM


I think
count(case when l.hdo = 'Football' then betid end) as Number_Of_Football_Bets_Over_Lifetime,

s.b.
sum(case when l.hdo = 'Football' then 1 else 0 end) as Number_Of_Football_Bets_Over_Lifetime,


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Antonio
Posting Yak Master

168 Posts
Posted - 2007-06-07 : 11:45:31
quote:
Originally posted by nr

the join would be a lot more efficient

count(case when l.hdo = 'Football' then betid end) as Number_Of_Football_Bets_Over_Lifetime,
sum(case when hdo = 'Football' then profit else 0 end) as GW_of_Football_Bets_Over_Lifetime,

...

from
dt_telebetmis t
left join sports_type_lookup l
on l.event_type = t.event_type
group by
cliaccid,
ACCOUNTNUM


I think
count(case when l.hdo = 'Football' then betid end) as Number_Of_Football_Bets_Over_Lifetime,

s.b.
sum(case when l.hdo = 'Football' then 1 else 0 end) as Number_Of_Football_Bets_Over_Lifetime,


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.


Thanks people!

_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site