MySQL: Count rows by field

Question

All rows in a table have a type field which is either 0 or 1.

I need to count rows with 0 and with 1 in one query. So that result should look something like:

type0 | type1
------+------
1234  | 4211

How can this be implemented?

Answer 1

select type, count(type) from tbl_table group by type;

Answer 2

You may want to use subqueries as scalar operands:

SELECT (SELECT COUNT(*) FROM table WHERE type = 0) AS type0,
       (SELECT COUNT(*) FROM table WHERE type = 1) AS type1;

Tested in MySQL as follows:

CREATE TABLE t (id INT NOT NULL AUTO_INCREMENT, type INT);

INSERT INTO t VALUES (NULL, 0);
INSERT INTO t VALUES (NULL, 0);
INSERT INTO t VALUES (NULL, 1);
INSERT INTO t VALUES (NULL, 1);
INSERT INTO t VALUES (NULL, 1);

SELECT (SELECT COUNT(*) FROM t WHERE type = 0) AS type0,
       (SELECT COUNT(*) FROM t WHERE type = 1) AS type1;

+-------+-------+
| type0 | type1 |
+-------+-------+
|     2 |     3 | 
+-------+-------+
1 row in set (0.00 sec)

Answer 3

Lessee...

SELECT
    SUM(CASE type WHEN 0 THEN 1 ELSE 0 END) AS type0,
    SUM(CASE type WHEN 1 THEN 1 ELSE 0 END) AS type1
FROM
   tableX;

This has not been tested.

Answer 4

A result like this can easily be achieved:

Type  Count
-----------
type0 1234
type1 4221

You can use something like:

SELECT CONCAT('type', [type]) as Type, COUNT(*) as Count
FROM MyTable
GROUP BY Type