MySQL - Count by two columns in one query

Question

Here is my table:

| ID | TYPE |
|  1 |  1   |
|  2 |  1   |
|  3 |  1   |
|  4 |  0   |
|  5 |  0   |

and now, I want to count how many records exists with TYPE=1 and how many records exist with TYPE = 0. Is it possible?

I know, I can do it in two queries:

SELECT COUNT(*) AS all_zero FROM `table WHERE type = 0;
SELECT COUNT(*) AS all_one FROM `table WHERE type = 1;

but I want do it in one query. Is it possible?

Answer 1

GROUP BY does exactly this:

SELECT type, COUNT(*) AS count_of_type FROM my_table GROUP BY type

This would give you multiple rows, one per type:

+------+---------------+
| type | count_of_type |
+------+---------------+
|    0 |             2 |
|    1 |             3 |
+------+---------------+

If you have more types, but only want 0 and 1, you may add a HAVING clause:

SELECT type, COUNT(*) AS count_of_type FROM my_table GROUP BY type HAVING type IN (0,1)

---

If you prefer to get different counts in a single result-set-row, please see the answer by spencer7593.

Answer 2

Conditional aggregation will do it...

SELECT SUM(t.type=0)          AS cnt_zero
     , SUM(t.type=1)          AS cnt_one
     , SUM(t.type IN (0,1))   AS cnt_one_and_zero
     , SUM(1)                 AS cnt_all
  FROM mytable t
;

---

Note that the usage of type=0 and type=1 is MySQL-specific shorthand, which other database probably wouldn't accept.

If we ignore the handling of NULL values, this is basically equivalent to

SELECT SUM(IF( t.type=0        ,1,0))   AS cnt_zero
     , SUM(IF( t.type=1        ,1,0))   AS cnt_one
     , SUM(IF( t.type IN (0,1), 1,0))   AS cnt_one_and_zero
     , SUM(1)                           AS cnt_all
  FROM mytable t
;

A more ANSI standard compliant equivalent

SELECT SUM(CASE WHEN t.type=0        THEN 1 ELSE 0 END)  AS cnt_zero
     , SUM(CASE WHEN t.type=1        THEN 1 ELSE 0 END)  AS cnt_one
     , SUM(CASE WHEN t.type IN (0,1) THEN 1 ELSE 0 END)  AS cnt_one_and_zero
     , SUM(1)                                            AS cnt_all
  FROM mytable t
;

To get the same handling of NULL values and the same result as the first query in this answer, we could write it like this:

SELECT SUM(CASE WHEN t.type=0        THEN 1 WHEN t.type IS NULL THEN NULL ELSE 0 END)  AS cnt_zero
     , SUM(CASE WHEN t.type=1        THEN 1 WHEN t.type IS NULL THEN NULL ELSE 0 END)  AS cnt_one
     , SUM(CASE WHEN t.type IN (0,1) THEN 1 WHEN t.type IS NULL THEN NULL ELSE 0 END)  AS cnt_one_and_zero
     , SUM(1)                                                                          AS cnt_all
  FROM mytable t
;

In all of these, the theme remains the same: we're aggregating the results of a condition. Aka conditional aggregation.

Answer 3

Very simple:

SELECT
    `type`,
    count(*) as `typecount`
FROM table
GROUP BY `type`