Query Addition Count Help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query Addition Count Help

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-04-28 : 14:58:27

I have the following


select date,

	count(case when Model <> ''
				and Term= '2'
				and Code IN ('AB101','BC199', 'HJ234') 
				and Ltype = 'P'
				and Changes = 'Y'
							then 1 end) as [Outside_model],

from history
where date between '20100201' and '20100428'
and state in ('NY', 'NJ')
group by date

I want to be able the count of outside_model to this:

count(case when Model <> '' and Model not like '%MOTO%'
and Term= '2'
and Code IN ('HB128')
and Ltype = 'P'
and Changes = 'Y'
then 1 end) as [Outside_model]

the first and second count are the same except the second count only counts where the model not like moto and the code is HB128. I cannot incorporate the HB128 into the first count because for those other codes, I want to count the MOTO.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-29 : 03:49:19

Do you mean this?

select date,

	sum(case when Model <> ''
				and Term= '2'
				and Code IN ('AB101','BC199', 'HJ234') 
				and Ltype = 'P'
				and Changes = 'Y'
							then 1 
			 when Model <> '' and Model not like '%MOTO%'
				and Term= '2'
				and Code IN ('HB128') 
				and Ltype = 'P'
				and Changes = 'Y'
							then 1
			 else 0
							end) as [Outside_model],

from history
where date between '20100201' and '20100428'
and state in ('NY', 'NJ')
group by date

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-30 : 02:07:00

os is it?


select date,

	count(case when Code IN ('AB101','BC199', 'HJ234') then 1 end) as [Outside_model],
        count(case when Code IN ('HB128') and Model not like '%MOTO%' then 1 end) as [Outside_model_MOTTO]
from history
where date between '20100201' and '20100428'
and state in ('NY', 'NJ')
and Model <> ''
and Term= '2'
and Ltype = 'P'
and Changes = 'Y'
group by date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources