# Tutorial :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?

### Solution:1

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

### Solution:2

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.

### Solution:3

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)  ``

### Solution: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  ``

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »