SQL help needed

[jdbruijn]

I've got a mail table that contains 3 date fields.

Code: Select all

id | mail_date1 | mail_date2 | mail_date3
------------------------------------------
1  | 2015-01-01 | 2015-01-08 | null
2  | 2015-01-01 | 2015-01-02 | 2015-01-02
3  | 2015-01-01 | 2015-01-05 | 2015-01-08


I'm trying to get a working query that returns a count of records for each day on those 3 date fields:

Code: Select all

date       | count(mail_date1) | count(mail_date2) | count(mail_date3)
2015-01-01 | 3                 | 0                 | 0
2015-01-02 | 0                 | 1                 | 1
2015-01-03 | 0                 | 0                 | 0
2015-01-04 | 0                 | 0                 | 0
2015-01-05 | 0                 | 1                 | 0
2015-01-06 | 0                 | 0                 | 0
2015-01-07 | 0                 | 0                 | 0
2015-01-08 | 0                 | 1                 | 1


The only solution I find is to use unions:

Code: Select all

SELECT *
FROM (
  SELECT
    dates.dt,
    count(sent.id) AS count1,
    0                             AS count2,
    0                             AS count3
  FROM (
         SELECT generate_series('2015-01-01' :: TIMESTAMP, '2015-01-31', '1 day') AS dt
       ) AS dates
    LEFT OUTER JOIN mail sent ON dates.dt = DATE(sent.mail_date1)
  GROUP BY dates.dt
  UNION
  SELECT
    dates.dt,
    0,
    count(remind.id),
    0
  FROM (
         SELECT generate_series('2015-01-01' :: TIMESTAMP, '2015-01-31', '1 day') AS dt
       ) AS dates
    LEFT OUTER JOIN mail remind ON dates.dt = DATE(remind.mail_date2)
  GROUP BY dates.dt
  UNION
  SELECT
    dates.dt,
    0,
    0,
    count(thank.id)
  FROM (
         SELECT generate_series('2015-01-01' :: TIMESTAMP, '2015-01-31', '1 day') AS dt
       ) AS dates
    LEFT OUTER JOIN mail thank ON dates.dt = DATE(thank.mail_date3)
  GROUP BY dates.dt
) AS T
ORDER BY t.dt


But this is not working correctly (when I add the third union it adds an extra line to the result instead of combining the result to the previous unions)
And I think that there should be a more allegant solution to this problem.

[ROCLASI]

Hi Jos,

Here is a quick and untested rewrite.
To get 1 single line for all 3 (or more unions) you need to group the result of this as well (and summarize the counts)
I also used a Common Table Expression (CTE) for the generate_series() bit.

Code: Select all

WITH dates AS (
    SELECT generate_series('2015-01-01' :: TIMESTAMP, '2015-01-31', '1 day') AS dt
},
SELECT dt, sum(count1), as count1, sum(count2) as count2, sum(count3) as count3
FROM (
    SELECT
        dates.dt
        , count(sent.id) AS count1
        , 0 AS count2
        , 0 AS count3
    FROM dates
    LEFT OUTER JOIN mail sent ON dates.dt = DATE(sent.mail_date1)
    GROUP BY dates.dt
    UNION ALL -- no need to use UNION which would do a sort to un-double the result internally (extra overhead)
    SELECT
        dates.dt
        , 0 AS count1
        , count(sent.id) AS count2
        , 0 AS count3
    FROM dates
    LEFT OUTER JOIN mail sent ON dates.dt = DATE(sent.mail_date2)
    GROUP BY dates.dt
    UNION ALL -- no need to use UNION which would do a sort to un-double the result internally (extra overhead)
    SELECT
        dates.dt
        , 0 AS count1
        , 0 AS count2
        , count(sent.id) AS count3
    FROM dates
    LEFT OUTER JOIN mail sent ON dates.dt = DATE(sent.mail_date3)
    GROUP BY dates.dt
) t
GROUP BY dt
ORDER BY dt


[jdbruijn]

Hi Robert,
With your solution I do get the correct count for each column.
But I don't get the rows that are completely empty. Can you tell me how to modify this query to include those rows as well?

[ROCLASI]

Hi Jos,

So if I understand you correctly you also want to see the counts of rows in the mail table that don't have a date in date1, 2 or 3. So since we are joining from the date table and joining over the date columns this will not match at all.
You then need to query for them specifically and add them to the unions. Of course they won't show up under a date but under the 'date' NULL.
Like so:

Code: Select all

WITH dates AS (
    SELECT generate_series('2015-01-01' :: TIMESTAMP, '2015-01-31', '1 day') AS dt
},
SELECT dt, sum(count1), as count1, sum(count2) as count2, sum(count3) as count3
FROM (
    SELECT
        dates.dt
        , count(sent.id) AS count1
        , 0 AS count2
        , 0 AS count3
    FROM dates
    LEFT OUTER JOIN mail sent ON dates.dt = DATE(sent.mail_date1)
    GROUP BY dates.dt
    UNION ALL
    SELECT
        dates.dt
        , 0 AS count1
        , count(sent.id) AS count2
        , 0 AS count3
    FROM dates
    LEFT OUTER JOIN mail sent ON dates.dt = DATE(sent.mail_date2)
    GROUP BY dates.dt
    UNION ALL
    SELECT
        dates.dt
        , 0 AS count1
        , 0 AS count2
        , count(sent.id) AS count3
    FROM dates
    LEFT OUTER JOIN mail sent ON dates.dt = DATE(sent.mail_date3)
    GROUP BY dates.dt
    UNION ALL
    SELECT
        null as dt
        , count(sent.id) as count1
        , 0 as count2
        , 0 as count3
    FROM mail sent
    WHERE sent.mail_date1 IS NULL
    UNION ALL
    SELECT
        null as dt
        , 0 as count1
        , count(sent.id) as count2
        , 0 as count3
    FROM mail sent
    WHERE sent.mail_date2 IS NULL
    UNION ALL
    SELECT
        null as dt
        , 0 as count1
        , 0 as count2
        , count(sent.id) as count3
    FROM mail sent
    WHERE sent.mail_date3 IS NULL
) t
GROUP BY dt
ORDER BY dt

And as you see I don't need to add another GROUP BY in these extra SELECT's because all group-able columns are literals so the parser knows they don't change.

Hope this helps.