CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Milan
Milan

Reputation: 136

Group by with multiple date columns

I am trying to do report for email status and I need to keep records on monthly basis.

Here is my table structure for data:

sent_on date;  
sent boolean;   
open date;  
clicked date;

The final output that I expect would be:

Month  | Year   | Sent Count | Open Count | Clicked Count

I know that I can do it if I make an individual statement such as

Select MONTH(sent_on), YEAR(sent_on), count(sent_on) FROM my_table
WHERE sent = true;

Select MONTH(open), YEAR(open), count(open) FROM my_table;

Select MONTH(clicked ), YEAR(clicked ), count(clicked ) FROM my_table;

Can you guys help me on this and provide any simple ideas? I'm sure I'm missing something. ps USING sql HERE

Upvotes: 0

Views: 2954

Answers (3)

Andriy M
Andriy M

Reputation: 77657

Unpivot the dates, then group the results conditionally:

SELECT
  MONTH(eventdate) AS Month,
  YEAR (eventdate) AS Year,
  COUNT(CASE eventkind WHEN 'sent_on' THEN 1 END) AS SentCount,
  COUNT(CASE eventkind WHEN 'open'    THEN 1 END) AS OpenCount,
  COUNT(CASE eventkind WHEN 'clicked' THEN 1 END) AS ClickedCount
FROM (
  SELECT
    x.eventkind,
    CASE x.eventkind
      WHEN 'sent_on' THEN sent_on
      WHEN 'open'    THEN open
      WHEN 'clicked' THEN clicked
    END AS eventdate
  FROM my_table AS t
  CROSS JOIN (VALUES ('sent_on', 'open', 'clicked')) AS x (eventkind)
  WHERE t.sent = true
) AS s
GROUP BY
  MONTH(eventdate),
  YEAR (eventdate)
;

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

SQL Fiddle

select
    date_trunc('month', d) "month",
    count(sent and sent_on = d or null) sent_on,
    count(open = d or null) open,
    count(clicked = d or null) clicked
from
    t
    inner join
    generate_series(
        (select least(min(sent_on), min(open), min(clicked)) from t),
        (select greatest(max(sent_on), max(open), max(clicked))from t)
        , '1 day'
    ) s(d) on s.d in (t.sent_on, t.open, t.clicked)
group by 1
order by 1

Upvotes: 1

Florin Ghita
Florin Ghita

Reputation: 17643

Not necessarly performant code:

SELECT m, y, sent_count, open_count, clicked_count
FROM
    (SELECT MONTH(sent_on) as m, YEAR(sent_on) as y
    FROM my_table
    WHERE sent = true
    GROUP BY MONTH(sent_on), YEAR(sent_on)
    UNION ALL
    SELECT MONTH(open), YEAR(open) 
    FROM my_table
    WHERE sent = true
    GROUP BY MONTH(open), YEAR(open)
    UNION ALL
    SELECT MONTH(open), YEAR(open) 
    FROM my_table
    WHERE sent = true
    GROUP BY MONTH(open), YEAR(open)) d
LEFT JOIN 
(Select MONTH(sent_on) as m , YEAR(sent_on) as y, count(sent_on)  sent_count
 FROM my_table 
 WHERE sent = true
 GROUP BY MONTH(sent_on), YEAR(sent_on)) s on (s.y=d.y and s.m=d.m)
LEFT JOIN 
(Select MONTH(open) as m , YEAR(open) as y, count(open)  open_count
 FROM my_table 
 WHERE sent = true
 GROUP BY MONTH(open), YEAR(open)) o on (o.y=d.y and o.m=d.m)
LEFT JOIN 
(Select MONTH(clicked) as m , YEAR(clicked) as y, count(clicked)  clicked_count
 FROM my_table 
 WHERE sent = true
 GROUP BY MONTH(clicked), YEAR(clicked)) c on (c.y=d.y and c.m=d.m);

Supposed open and clicked are sent.

UPDATE: For performance we can start form folowing query:

Select MONTH(sent_on) sm, YEAR(sent_on) sy, 
       MONTH(OPEN) om, YEAR(OPEN) oy, 
       MONTH(clicked) cm, YEAR(clicked) cy, 
       count(sent_on) sent_cnt, count(clicked) clicked_cnt, count(open) open_cnt
FROM my_table
WHERE sent = true
GROUP BY MONTH(sent_on), YEAR(sent_on), 
       MONTH(OPEN), YEAR(OPEN), 
       MONTH(clicked), YEAR(clicked);

Upvotes: 0

Related Questions