First time posting in Stack Exchange so excuse any faux pas (well let me know them of course).
I'm using union for the first time on an already Frankenstein'ed query I've pull together so I'm looking to get insight into what might be preventing the 2nd part of the union. The query's business problem is sales for a customer have a particular point multiplier for their type of product, and are elliglbe for a redemption once they reach a threshold. So the query is adding this multiplier in, filtering based on the HAVING threshold query, and then dividing by the redemptions.
SELECT
SUM(points/1000) AS QUARTER_1
FROM (
SELECT ID,
SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
WHEN multiplier = 'ONE' THEN (sales * 1)
ELSE 0 END ) as points
FROM transactions
where start_trx_date between '2017-07-01' AND '2017-10-01'
group by ID
HAVING
SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
WHEN multiplier = 'ONE' THEN (sales * 1)
ELSE 0 END ) > 1000
) t
UNION
SELECT
SUM(points/1000) AS QUARTER_2
FROM (
SELECT ID,
SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
WHEN multiplier = 'ONE' THEN (sales * 1)
ELSE 0 END ) as points
FROM transactions
where start_trx_date between '2017-07-01' AND '2017-10-01'
group by ID
HAVING
SUM(CASE WHEN multiplier = 'TWO' THEN (sales * 2)
WHEN multiplier = 'ONE' THEN (sales * 1)
ELSE 0 END ) > 1000
) t
The query is working in the sense that no errors are coming up, the problem is my result is one cell of Quarter_1 – any clues on what I need to do?
Best Answer
By default UNION - merge distinct values (https://www.postgresql.org/docs/10/static/queries-union.html) so if your quarter values same(and looking for code - it is exactly) - one of the results would be omitted.
Use UNION ALL if need keep all results