How to count 2 different data in one query

Question

I need to calculate sum of occurences of some data in two columns in one query. DB is in SQL Server 2005.

For example I have this table:

Person: Id, Name, Age

And I need to get in one query those results:
1. Count of Persons that have name 'John'
2. Count of 'John' with age more than 30 y.

I can do that with subqueries in this way (it is only example):

SELECT (SELECT COUNT(Id) FROM Persons WHERE Name = 'John'), 
  (SELECT COUNT (Id) FROM Persons WHERE Name = 'John' AND age > 30) 
FROM Persons

But this is very slow, and I'm searching for faster method.

I found this solution for MySQL (it almost solve my problem, but it is not for SQL Server).

Do you know better way to calculate few counts in one query than using subqueries?

Solution 1

Using a CASE statement lets you count whatever you want in a single query:

SELECT
    SUM(CASE WHEN Persons.Name = 'John' THEN 1 ELSE 0 END) AS JohnCount,
    SUM(CASE WHEN Persons.Name = 'John' AND Persons.Age > 30 THEN 1 ELSE 0 END) AS OldJohnsCount,
    COUNT(*) AS AllPersonsCount
FROM Persons

Solution 2

Use:

SELECT COUNT(p.id),
       SUM(CASE WHEN p.age > 30 THEN 1 ELSE 0 END)
  FROM PERSONS p
 WHERE p.name = 'John'

It's always preferable when accessing the same table more than once, to review for how it can be done in a single pass (SELECT statement). It won't always be possible.

Edit:

If you need to do other things in the query, see Chris Shaffer's answer.

Comments

[Ken White]

+1. Darn, you're fast. Your answer appeared while I was typing the WHERE clause in my almost identical one. Next time I'll answer before editing. :)

[OMG Ponies]

@Ken White: If I reformatted the question first, the roles would be reversed :)

[Ken White]

Technically correct, but it's easier if you just SELECT the rows where the name is 'John' and then use the CASE on the results for the SUM.

[Chris Shaffer]

I did that intentionally as an example (also why I selected the total count in AllPersonsCount). This demonstrates that it is possible to count absolutely anything separately.