Postgresql – union only dispalying the first query

postgresqlunion

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