SQL Query returning two counts.

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)

SQL Query returning two counts.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-23 : 08:03:53

Emmanuel writes "Hi!

I have a Customers table with these fields:

CustomerID
CustomerName

I would like to know with a single query how many of my Customers are named John and how many are not.

It seems a very basic query to me but I can't figure it out without using temporary tables or stored procedures.

Regards,
Emmanuel"

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-23 : 08:24:19

SELECT COUNT(*) as CountofJohns FROM MyTable WHERE CustomerName = 'John'

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-23 : 08:27:24

select

sum(case CustomerName when 'John' then 1 else 0 end),

count(*) - sum(case CustomerName when 'John' then 1 else 0 end)

from t

dsdeming

479 Posts

Posted - 2003-09-23 : 08:32:05

Yet another way.

SELECT COUNT( s.CustomerName ), COUNT( s2.CustomerName ) AS John
FROM TableName s
LEFT OUTER JOIN ( SELECT * FROM TableName WHERE CustomerName = 'John' ) s2 ON s.PK = s2.PK

Dennis

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-09-23 : 09:09:32

quote:
Originally posted by Stoad

select
count(*) - sum(case CustomerName when 'John' then 1 else 0 end)

Staod!


select

sum(case when CustomerName ='John' then 1 else 0 end),
sum(case when CustomerName  <> 'John' then 1 else 0 end),

from t

Vit vit vit!

__________________
Make love not war!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-23 : 09:18:14

select 'John', count(*) from t where CustomerName='John'
union all
select 'Not John', count(*) from t where CustomerName<>'John'

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-23 : 09:23:22

>Staod!

LOL :) What is that staod??

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-09-23 : 09:34:18

pfff..... Stoad!

__________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources