CODEWITHSUNDEEP
sqlpostgresql
Javier Fernandez
Javier Fernandez

Reputation: 53

Postgres - How do I combine the results of two separate queries into the same columns

I am using CTEs to pull the same columns from two different dates because I need to pull the latest dates for each quarter. So the query looks like this:

WITH Q1 AS
(
SELECT date, region, revenue
FROM sales
WHERE date = (SELECT MAX(date) from sales WHERE date between date_trunc('quarter', CURRENT_DATE)::date - interval '3 months' AND DATE_TRUNC('quarter', CURRENT_DATE)::date - interval '1 day')
)
, Q2 AS
(
SELECT date, region, revenue
FROM sales
WHERE date = (SELECT MAX(date) FROM sales WHERE date BETWEEN DATE_TRUNC('quarter', CURRENT_DATE)::date AND DATE_TRUNC('quarter', CURRENT_DATE)::date + interval '3 months')
)
SELECT q1.date, q1.region, q1.sales, q2.date, q2.region, q2.sales
FROM Q1
INNER JOIN Q2
USING (region)

Which outputs what I expected:

date       | region     | sales   | date       | region     | sales
-----------|------------|---------|------------|------------|----------
2018-03-31 | US - West  | 2387598 | 2018-06-14 | US - West  | 2389075
2018-03-31 | US - East  | 3098527 | 2018-06-14 | US - East  | 3095735
2018-03-31 | US - South | 1029745 | 2018-06-14 | US - South | 1023754

However, since they have the same columns, I would want them stacked as rows not extra columns. I wish for it to look like this:

date       | region     | sales   
-----------|------------|---------
2018-03-31 | US - West  | 2387598 
2018-03-31 | US - East  | 3098527 
2018-03-31 | US - South | 1029745 
2018-06-14 | US - West  | 2389075
2018-06-14 | US - East  | 3095735
2018-06-14 | US - South | 1023754

I know I did this wrong by joining on region, but I don't know how I would combine the CTE without it.

Upvotes: 0

Views: 199

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Use union all:

SELECT q1.date, q1.region, q1.sales
FROM Q1
UNION ALL
SELECT q2.date, q2.region, q2.sales
FROM Q2;

Or, you could dispense with the CTEs and just use OR:

SELECT date, region, revenue
FROM sales
WHERE date = (SELECT MAX(date) from sales WHERE date between date_trunc('quarter', CURRENT_DATE)::date - interval '3 months' AND DATE_TRUNC('quarter', CURRENT_DATE)::date - interval '1 day') OR
      date = (SELECT MAX(date) FROM sales WHERE date BETWEEN DATE_TRUNC('quarter', CURRENT_DATE)::date AND DATE_TRUNC('quarter', CURRENT_DATE)::date + interval '3 months');

Upvotes: 2

Related Questions