Use SELECT results to make a table

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|...

Extract the month from date and do a GROUP BY with month. Finally count. — AT-2017, Sep 28 '16 at 08:51
This is a pivot table, for an example of solution see http://stackoverflow.com/questions/7674786/mysql-pivot-table — hruske, Sep 28 '16 at 08:53

score 1 · Accepted Answer · answered Sep 28 '16 at 08:53

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

Jim Macaulay · Answer 2 · 2016-09-28T09:01:20.480

Hi,
You can have GROUP BY function

    SELECT 
    to_char(date,'YYYY-MM') as date,
    CASE WHEN A=1 THEN COUNT(A)  END as A ,
    CASE WHEN B=1 THEN COUNT(B)  END as B ,
    CASE WHEN C=1 THEN COUNT(C)  END as C ,
    CASE WHEN D=1 THEN COUNT(D)  END as D ,
    CASE WHEN E=1 THEN COUNT(E)  END as E  
    from table_name group by date order by date

This query groups the column based on year and month and provides you the result

Use SELECT results to make a table

2 Answers2