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
 General SQL Server Forums
 New to SQL Server Programming
 count in where condition		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

278 Posts
Posted - 06/09/2012 :  12:18:29  Show Profile  Reply with Quote
how to use COUNT those who wear blue skirts only?

my count has error. how to count only blue skirts?

select column1, column2,
count (select * from tableB where tableB.skirt='blue') as numberOfSkirtsInBlue
from tableA
left join tableB
on tableA.ID=tableB.ID
group by column1, column2

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 06/09/2012 :  13:29:41  Show Profile  Reply with Quote 

select column1, column2,
sum (case when tableB.skirt='blue' then 1 else 0 end) as numberOfSkirtsInBlue
from tableA
left join tableB
on tableA.ID=tableB.ID
group by column1, column2


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

peace
Constraint Violating Yak Guru

278 Posts
Posted - 06/10/2012 :  02:05:50  Show Profile  Reply with Quote
It has ID for each skirts. Can i distinct it to get the accurate amount?

I try this but error on the distinct part.

sum (case when tableB.skirt='blue' and distinct tableB.ID then 1 else 0 end) as numberOfSkirtsInBlue
Go to Top of Page

peace
Constraint Violating Yak Guru

278 Posts
Posted - 06/10/2012 :  02:15:23  Show Profile  Reply with Quote
i tried this but get this error:

Error converting data type varchar to bigint


sum (case when tableB.skirt='blue' and tableB.ID='select distinct tableB.ID' then 1 else 0 end) as numberOfSkirtsInBlue
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 06/10/2012 :  02:49:11  Show Profile  Reply with Quote 

select column1, column2,
count(distinct case when tableB.skirt='blue' then tableB.ID else null end) as numberOfSkirtsInBlue
from tableA
left join tableB
on tableA.ID=tableB.ID
group by column1, column2


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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.05 seconds. Powered By: Snitz Forums 2000