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 »