How to display queries of separate tables in adjacent columns?

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
--------------+----------------

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

StackExchange Database Administrators Q#156553, answer score: 5