user6571534 user6571534 - 2 months ago 7
MySQL Question

Use SELECT results to make a table

I have a table like this:

enter image description here

I want to execute the following SELECT statements:

SELECT count(*) from table WHERE A=1 and date between 2013-02 and 2013-03
SELECT count(*) from table WHERE A=1 and date between 2013-03 and 2013-04
SELECT count(*) from table WHERE A=1 and date between 2013-04 and 2013-05
....
SELECT count(*) from table WHERE B=1 and date between 2013-02 and 2013-03
SELECT count(*) from table WHERE B=1 and date between 2013-03 and 2013-04
SELECT count(*) from table WHERE B=1 and date between 2013-04 and 2013-05
...etc


What's the fastest query and can I have the results arranged in a table like:

date |A=1 |B=1 |C=1 |...
2013-Feb|count|count|count|
2013-Mar|count|...

Answer
SELECT DATE_FORMAT(date, '%Y-%m'),
       SUM(CASE WHEN A=1 THEN 1 END) AS Acount,
       SUM(CASE WHEN B=1 THEN 1 END) AS Bcount,
       SUM(CASE WHEN C=1 THEN 1 END) AS Ccount,
       SUM(CASE WHEN D=1 THEN 1 END) AS Dcount,
       SUM(CASE WHEN E=1 THEN 1 END) AS Ecount
FROM table
GROUP BY DATE_FORMAT(date, '%Y-%m')

If the only values in columns A through E are 0 or 1 (or possibly NULL), then we can simplify to:

SELECT DATE_FORMAT(date, '%Y-%m'),
       SUM(A) AS Acount,
       SUM(B) AS Bcount,
       SUM(C) AS Ccount,
       SUM(D) AS Dcount,
       SUM(E) AS Ecount
FROM table
GROUP BY DATE_FORMAT(date, '%Y-%m')