I have a table with a field called type
, where 3 various rows are possible: 1
, 2
and 3
.
Now, I don't care about 3
at al开发者_如何转开发l. I need to count how many rows there are with type = 1
and with type = 2
. I am doing this with 2 queries, like this:
Query1: SELECT COUNT(id) as count FROM users WHERE type='1'
SELECT COUNT(id) as count FROM users WHERE type='2'
Can I do this with only 1 single query? If so, should I, or not? How would the query look?
SELECT type,
COUNT(id) AS count
FROM users
WHERE type IN ('1','2')
GROUP BY type
SELECT type, COUNT(id) AS count
FROM users
GROUP BY type
HAVING type < 3
SELECT sum(case when type = '1' then 1 else 0 end) as count_for_one,
sum(case when type = '2' then 1 else 0 end) as count_for_tow
FROM users
WHERE type IN ('1','2')
If you want separate numbers,
SELECT SUM(IF(type='1', 1, 0)), SUM(IF(type='2', 1, 0))
FROM users WHERE type IN ('1', '2')
精彩评论