Need some sort of "conditional grouping" in MySQL.

Question

I have Article table:

id | type | date
-----------------------
 1 | A    | 2010-01-01
 2 | A    | 2010-01-01
 3 | B    | 2010-01-01

Field type can be A, B or C.

I need to run a report that would return how many articles of each type there is per every day, like this:

date       | count(type="A") | count(type="B") | count(type="C")
-----------------------------------------------------
2010-01-01 | 2               | 1               | 0
2010-01-02 | 5               | 6               | 7

Currently I am running 3 queries for every type and then manually merging the results

select date, count(id) from article where type="A" group by date

Is it possible to do this in one query? (in pure sql, no stored procedures or anything like that).

Thanks

Answer 1

A combination of SUM and CASE should do ya

select date
    , sum(case when type ='A' then 1 else 0 end) as count_type_a 
    , sum(case when type ='B' then 1 else 0 end) as count_type_b
    , sum(case when type ='C' then 1 else 0 end) as count_type_c 
from article group by date

Answer 2

EDIT: Alex's answer above uses a better approach that the one in this answer. I'm leaving it here just because it also satisfies the question, in an alternative way:

---

You should be able to use sub queries, as follows:

SELECT    DATE(a.date) as date,
          (SELECT COUNT(a1.id) FROM articles a1 WHERE a1.type = 'A' AND a1.date = a.date) count_a,
          (SELECT COUNT(a2.id) FROM articles a2 WHERE a2.type = 'B' AND a2.date = a.date) count_b,
          (SELECT COUNT(a3.id) FROM articles a3 WHERE a3.type = 'C' AND a3.date = a.date) count_c
FROM      articles a
GROUP BY  a.date;

Test Case:

CREATE TABLE articles (id int, type char(1), date datetime);

INSERT INTO articles VALUES (1, 'A', '2010-01-01');
INSERT INTO articles VALUES (2, 'A', '2010-01-01');
INSERT INTO articles VALUES (3, 'B', '2010-01-01');
INSERT INTO articles VALUES (4, 'B', '2010-01-02');
INSERT INTO articles VALUES (5, 'B', '2010-01-02');
INSERT INTO articles VALUES (6, 'B', '2010-01-03');
INSERT INTO articles VALUES (7, 'B', '2010-01-01');
INSERT INTO articles VALUES (8, 'C', '2010-01-05');

Result:

+------------+---------+---------+---------+
| date       | count_a | count_b | count_c |
+------------+---------+---------+---------+
| 2010-01-01 |       2 |       2 |       0 |
| 2010-01-02 |       0 |       2 |       0 |
| 2010-01-03 |       0 |       1 |       0 |
| 2010-01-05 |       0 |       0 |       1 |
+------------+---------+---------+---------+
4 rows in set (0.00 sec)