user6571534 - 10 months ago 50

MySQL Question

I have a table like this:

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 Source

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