PostgreSQL merge two queries with COUNT and GROUP BY in each

Question

1st query:

SELECT date_trunc('day', date1) as date, COUNT(*) AS count_a 
FROM table_a GROUP BY date

result:

date                    count_a
2014-04-01 00:00:00.0   1011642
2014-04-02 00:00:00.0   309048

2nd query:

SELECT date_trunc('day', date1) as date, COUNT(*) AS count_b 
FROM table_b GROUP BY date

result:

date                    count_b
2014-04-01 00:00:00.0   2342
2014-04-02 00:00:00.0   43432

I'd like to get:

date                    count_a  count_b
2014-04-01 00:00:00.0   1011642  2342
2014-04-02 00:00:00.0   309048   43432

Is this possible?

Answer 1

Use FULL [OUTER] JOIN:

SELECT *
FROM  (
   SELECT date1::date AS day, COUNT(*) AS count_a
   FROM   table_a
   GROUP  BY 1
   ) a
FULL JOIN (
   SELECT date1::date AS day, COUNT(*) AS count_b
   FROM   table_b
   GROUP  BY 1
   ) b USING (day);

• The USING clause automatically folds to one column day in the result.
• To get dates just cast to date. Faster, simpler.

SQLfiddle (based on Joachim's).

Minor difference: this query returns NULL for days without any rows on one side. Use COALESCE(count_b, 0) to get 0 instead.

Answer 2

A simple UNION ALL should make it possible.

SELECT date1 date, SUM(a) count_a, SUM(b) count_b 
FROM (
  SELECT DATE_TRUNC('day', date1) date1, 1 a, 0 b FROM table_a 
  UNION ALL 
  SELECT DATE_TRUNC('day', date1) date1, 0 a, 1 b FROM table_b
) z 
GROUP BY date;

EDIT: Here's an SQLfiddle.