Postgres counting elements and columnize by date

Question

Ok Postgres geniuses, I need some help. I am trying to chart a large amount of data and want to prepare the data as best I can in the db before sending over to the frontend. I am using Postgres 9.6

I have a set of results like this:

Source  | Date
--------|------------
Email   | 2017-06-01
Email   | 2017-06-01
Email   | 2017-06-03
Email   | 2017-06-03
Email   | 2017-06-03
Email   | 2017-06-04
Email   | 2017-06-06
Email   | 2017-06-06
Email   | 2017-06-07
Banner  | 2017-06-01
Banner  | 2017-06-02
Banner  | 2017-06-03
....

And I need to get it all counted by day then columnized and if there is a zero result for a date I need zerofilled.

Desired result:

Source  | 2017-06-01  | 2017-06-02  | 2017-06-03  | ...  | 2017-06-07
--------|-------------|-------------|-------------|------|------------
Email   | 2           | 0           | 3           | ...  | 1
Banner  | 1           | 1           | ...         

I hope this makes sense, thank you.

Answer 1

You can either use:

SELECT 
    *
FROM
    crosstab(
    $$
        SELECT "Source", "Date"::text, count("Date") 
        FROM t 
        GROUP BY "Source", "Date"
        ORDER BY "Source"
    $$,
    $$
        SELECT cast(d as date)
        FROM generate_series(date '2017-06-01', date '2017-06-10', interval '1 day') as s(d)
    $$
    ) AS  
    (
        "Source" text, 
        "2017-06-01" bigint,
        "2017-06-02" bigint,
        "2017-06-03" bigint,
        "2017-06-04" bigint,
        "2017-06-05" bigint,
        "2017-06-06" bigint,
        "2017-06-07" bigint,
        "2017-06-08" bigint,
        "2017-06-09" bigint,
        "2017-06-10" bigint
    ) ;

Which uses the crosstab(text source_sql, text category_sql), specific to PostgreSQL and that needs the tablefunc extension, or you can go the "classical SQL pivot":

SELECT
    "Source"
    ,COUNT(CASE WHEN "Date" = '2017-06-01' THEN 1 END) AS "2017-06-01"
    ,COUNT(CASE WHEN "Date" = '2017-06-02' THEN 1 END) AS "2017-06-02"
    ,COUNT(CASE WHEN "Date" = '2017-06-03' THEN 1 END) AS "2017-06-03"
    ,COUNT(CASE WHEN "Date" = '2017-06-04' THEN 1 END) AS "2017-06-04"
    ,COUNT(CASE WHEN "Date" = '2017-06-05' THEN 1 END) AS "2017-06-05"
    ,COUNT(CASE WHEN "Date" = '2017-06-06' THEN 1 END) AS "2017-06-06"
    ,COUNT(CASE WHEN "Date" = '2017-06-07' THEN 1 END) AS "2017-06-07"
    ,COUNT(CASE WHEN "Date" = '2017-06-08' THEN 1 END) AS "2017-06-08"
    ,COUNT(CASE WHEN "Date" = '2017-06-09' THEN 1 END) AS "2017-06-09"
    ,COUNT(CASE WHEN "Date" = '2017-06-10' THEN 1 END) AS "2017-06-10"
FROM
    t
GROUP BY
    "Source"
ORDER BY
    "Source" ;

Source | 2017-06-01 | 2017-06-02 | 2017-06-03 | 2017-06-04 | 2017-06-05 | 2017-06-06 | 2017-06-07 | 2017-06-08 | 2017-06-09 | 2017-06-10
:----- | ---------: | ---------: | ---------: | ---------: | ---------: | ---------: | ---------: | ---------: | ---------: | ---------:
Banner |          1 |          1 |          1 |          0 |          0 |          0 |          0 |          0 |          0 |          0
Email  |          2 |          0 |          3 |          1 |          0 |          2 |          1 |          0 |          0 |          0

Both will return the same values, with the difference being the second will give you '0' and the first will give you 'null' when there are no values to count.

dbfiddle here