SQL full outer join, default value to column in other table

Question

I'm attempting to do a full outer join in Postgres to see how data changes between the past 24 hours and the 24 hours before that ("today" and "yesterday" euphemistically). Some values only exist on one side of the join, in that case I'd like to use the value from the other side of the join.

Current result with below query:

Today Type	Today Count	Yesterday Type	Yesterday Count
REPORT_A	2	REPORT_A	5
REPORT_B	4
		REPORT_C	6

What I would like the output to be:

Today Type	Today Count	Yesterday Type	Yesterday Count
REPORT_A	2	REPORT_A	5
REPORT_B	4	REPORT_B	0
REPORT_C	0	REPORT_C	6

For REPORT_B I'd like its values propagated to the right side and for REPORT_C I'd like its values propagated to the left side. How do I do this?

WITH
  yesterday_report_count AS (
    SELECT
      "reports"."report"."type" AS "type",
      "reports"."report"."status" AS "status",
      COUNT(*) AS "count"
    FROM
      "reports"."report"
    WHERE
      "reports"."report"."requested_at" BETWEEN NOW() - INTERVAL '24 HOURS' AND NOW()
    GROUP BY
      "reports"."report"."type",
    ORDER BY
      "count" DESC,
      "reports"."report"."type" ASC,
  )


SELECT 
yesterday_report_count.type as "Today Type",
yesterday_report_count.count "Today Count",
today_report_count.type as "Yesterday Type",
today_report_count.count as "Yesterday Count"
FROM
  yesterday_report_count
FULL JOIN (
    SELECT
      "reports"."report"."type" AS "type",
      COUNT(*) AS "count"
    FROM
      "reports"."report"
    WHERE
    1=1
    AND "reports"."report"."requested_at" BETWEEN NOW() - INTERVAL '48 HOURS' AND NOW()  - INTERVAL '24 HOURS'
    GROUP BY
      "reports"."report"."type",
    ORDER BY
      "count" DESC,
      "reports"."report"."type" ASC,
  ) AS today_report_count
  ON today_report_count.type = yesterday_report_count.type

I tried using coalesce() in the select statement but it seems that the "missing" values are not actually considered null.

ORDER BY without TOP/LIMIT not at the outermost level does nothing. Whatever you think it's doing, it's not. PS Please always use a tiny mnemonic table alias with every column reference. — philipxy
– philipxy, Commented Jul 25, 2024 at 0:43
Please ask 1 specific researched non-duplicate question. Either ask re 1 bad query/function with obligatory minimal reproducible example, including why you think it should return something else or are unsure at the 1st subexpression where you don't get what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & a minimal reproducible example--then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. How to Ask Help center Basic questions are faqs. — philipxy
– philipxy, Commented Jul 25, 2024 at 0:44
A MRE includes: cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. But it's clear that your "values propagated" doesn't require any of the code you wrote good or bad, just code & i/o of a few lines. But further it's obvious that that task will have been asked & answered as a beginner question long ago. — philipxy
– philipxy, Commented Jul 25, 2024 at 6:21
Please before considering posting: Pin down code issues via minimal reproducible example. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is unusual, read the help. Google re googling/searching, including Q&A at Meta Stack Overflow & Meta Stack Exchange. How much research effort is expected of Stack Overflow users? How to Ask Help center Reflect research in posts. — philipxy
– philipxy, Commented Jul 25, 2024 at 6:25

The Impaler · Accepted Answer · 2024-07-24 21:29:06Z

1

Use COALESCE(). For example:

select
  coalesce(t.a, u.a) as a,
  coalesce(t.b, 0) as tb,
  coalesce(t.a, u.a) as a, -- no need to repeat this, but if you must...
  coalesce(u.b, 0) as ub 
from t
full join u on u.a = t.a

Result:

 a  tb   a  ub  
 -- ---- -- --- 
 A  100  A  120 
 B  200  B  0   
 C  0    C  300

See running example at db<>fiddle.

answered Jul 24, 2024 at 21:29

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL full outer join, default value to column in other table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related