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?
Add a comment
|
4 Answers
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.
2 Comments
Daniel Vassallo
Do you really need the FROM clause? ... In MySQL that would add an unnecessary table scan.
staticsan
Er, yes, you need the FROM clause. That's how you indicate which table you're reading the column
type from.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)
2 Comments
martin clayton
@Daniel - won't that have to scan the table twice (e.g. see comment on the page you linked to)?
Daniel Vassallo
@martin: Yes it does. The accepted answer would be the most efficient. Apparently I gave too much importance to the OP's suggested output.
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
1 Comment
Daniel Vassallo
Note that in MySQL, the
+ is not a concatenation operator.