How to count 2 different data in one query

问题:

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?

回答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

回答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.