How to display queries of separate tables in adjacent columns?

Question

I have two tables - one titled "planning constraints" which contains the 'sot_allowed' time intervals, and one titled "planning" which contains the 'sot_contribution' time interval.

Here are the schema of the two tables (edited for readiability):

                         Table "public.planning_constraints"
   Column    |           Type           |          Modifiers  
-------------+--------------------------+-------------------------------
 start_time  | timestamp with time zone | 
 end_time    | timestamp with time zone | 
 sot_allowed | interval                 | 

                         Table "public.planning"
      Column      |           Type           |         Modifiers          
------------------+--------------------------+----------------------------
 start_time       | timestamp with time zone | 
 end_time         | timestamp with time zone | 
 sot_contribution | interval                 | 

I can query them separately and produce the totals that I want. The query of the "planning_constraints" table is:

SELECT
  date_trunc('day', start_time - INTERVAL '18 hours')::date AS planning_day,
  sum(sot_allowed) AS minutes_allowed
FROM planning_constraints
WHERE start_time>='2016-11-26 18:00:00+00' AND start_time<'2016-12-03 18:00:00+00' AND comment like '6%'
GROUP BY planning_day
ORDER BY planning_day;

which produces:

 planning_day | minutes_allowed 
--------------+-----------------
 2016-11-26   | 01:24:00
 2016-11-27   | 01:38:00
 2016-11-28   | 01:29:00
 2016-11-29   | 01:43:00
 2016-11-30   | 01:32:00
 2016-12-01   | 01:41:00
 2016-12-02   | 01:43:00
(7 rows)

The query of the "planning" table is:

SELECT
  date_trunc('day', start_time - INTERVAL '18 hours')::date AS planning_day,
  sum(sot_contribution) AS minutes_planned
FROM planning
WHERE start_time>='2016-11-26 18:00:00' AND start_time<'2016-12-03 18:00:00+00'
GROUP BY planning_day
ORDER BY planning_day;

which produces:

 planning_day | minutes_planned 
--------------+-----------------
 2016-11-26   | 01:19:12
 2016-11-27   | 01:38:23
 2016-11-28   | 01:27:36
 2016-11-29   | 01:40:39
 2016-11-30   | 01:27:16
 2016-12-01   | 01:38:28
 2016-12-02   | 01:42:50
(7 rows)

I would like to present the results of these queries in adjacent columns, along with the difference between them. Here is my attempt to do so:

SELECT
  date_trunc('day', c.start_time - INTERVAL '18 hours')::date AS planning_day,
  sum(c.sot_allowed) AS minutes_allowed,
  sum(p.sot_contribution) AS minutes_planned,
  (sum(c.sot_allowed) - sum(p.sot_contribution)) AS diff
FROM planning_constraints c, planning p
WHERE c.start_time>='2016-11-26 18:00:00+00' AND c.start_time<'2016-12-03 18:00:00+00' AND c.comment like '6%'
AND p.start_time>='2016-11-26 18:00:00' AND p.start_time<'2016-12-03 18:00:00+00'
GROUP BY planning_day
ORDER BY planning_day;

which produces:

 planning_day | minutes_allowed | minutes_planned |   diff    
--------------+-----------------+-----------------+-----------
 2016-11-26   | 576:48:00       | 43:37:36        | 533:10:24
 2016-11-27   | 672:56:00       | 43:37:36        | 629:18:24
 2016-11-28   | 611:08:00       | 43:37:36        | 567:30:24
 2016-11-29   | 707:16:00       | 43:37:36        | 663:38:24
 2016-11-30   | 631:44:00       | 43:37:36        | 588:06:24
 2016-12-01   | 693:32:00       | 43:37:36        | 649:54:24
 2016-12-02   | 707:16:00       | 43:37:36        | 663:38:24
(7 rows)

The 'minutes_allowed' and 'minutes_planned' columns do not present the correct values. I feel like I am missing something small here, however, I have been unable to find a solution.

Answer 1

You are basically building two tables, which then has to be joined on the date column. In your query you miss the join condition.

One clear way of solving this is building the two result sets as CTEs (WITH queries):

WITH pc AS (SELECT planning_day, sum(sot_contribution) AS minutes_planned 
            FROM planning_constraints 
            ...),
      p AS (SELECT planning_day, sum(sot_allowed) AS minutes_allowed
            FROM planning
            ...)
SELECT planning_day, 
       COALESCE(minutes_allowed, interval '0 minute') AS allowed, 
       COALESCE(minutes_planned, interval '0 minute') AS planned, 
       COALESCE(minutes_allowed, interval '0 minute') - 
           COALESCE(minutes_planned, interval '0 minute') AS diff
  FROM pc FULL OUTER JOIN p USING (planning_day)
 ORDER BY planning_day;

FULL OUTER JOIN means if there is a day specified in either one of the result sets, there will be a line for that day. If this is not what you want, figure out which type of join you need.

Notes:

• planning_day does not need to be COALESCEd, as the USING clause takes care of this
• also, there is no need to use a GROUP BY in the outer query, as planning_day is already the key of both source tables
• the ORDER BY can be now removed from both subqueries

Answer 2

Using @dezso's solution, which is the accepted answer above, here is the amended query and result for reference.

WITH pc AS (SELECT date_trunc('day', start_time - INTERVAL '18 hours')::date as planning_day, 
    sum(sot_allowed) AS minutes_allowed
  FROM planning_constraints
  WHERE start_time>='2016-11-26 18:00:00+00' AND start_time<'2016-12-03 18:00:00+00' AND comment like '6%'
  GROUP BY planning_day),
 p AS (SELECT date_trunc('day', start_time - INTERVAL '18 hours')::date as planning_day, 
    sum(sot_contribution) AS minutes_planned
  FROM planning
  WHERE start_time>='2016-11-26 18:00:00' AND start_time<'2016-12-03 18:00:00+00'
  GROUP BY planning_day)
SELECT planning_day, 
   COALESCE(minutes_allowed, interval '0 minute') AS allowed, 
   COALESCE(minutes_planned, interval '0 minute') AS planned, 
   COALESCE(minutes_allowed, interval '0 minute') - 
       COALESCE(minutes_planned, interval '0 minute') AS diff
FROM pc FULL OUTER JOIN p USING (planning_day)
ORDER BY planning_day;

 planning_day | allowed  | planned  |   diff    
--------------+----------+----------+-----------
 2016-11-26   | 01:24:00 | 01:19:12 | 00:04:48
 2016-11-27   | 01:38:00 | 01:38:23 | -00:00:23
 2016-11-28   | 01:29:00 | 01:27:36 | 00:01:24
 2016-11-29   | 01:43:00 | 01:40:39 | 00:02:21
 2016-11-30   | 01:32:00 | 01:27:16 | 00:04:44
 2016-12-01   | 01:41:00 | 01:38:28 | 00:02:32
 2016-12-02   | 01:43:00 | 01:42:50 | 00:00:10
(7 rows)