Combine and fuse the results of two SQL queries with UNION

I am writing two seperate SQL queries to get data for two different dates like so:

SELECT number, sum(sales) as sales, sum(discount) sa discount, sum(margin) as margin
FROM table_a
WHERE day = '2019-08-09'
GROUP BY number

SELECT number, sum(sales) as sales, sum(discount) sa discount, sum(margin) as margin
FROM table_a
WHERE day = '2018-08-10'
GROUP BY number

I tried fusing them like so to get the results for the same number in one row from two different dates:

SELECT number, sum(sales) as sales, sum(discount) sa discount, sum(margin) as margin, 0 as sales_n1, 0 as discount_n1, 0 as margin_n1
FROM table_a
WHERE day = '2019-08-09'
GROUP BY number
UNION
SELECT number, 0 as sales, 0 as discount, 0 as margin, sum(sales_n1) as sales_n1, sum(discount_n1) as discount_n1, sum(margin_n1) as margin_n1
FROM table_a
WHERE day = '2018-08-10'
GROUP BY number

But it didn’t work as I get the rows for the first query with zeroes for the columns defined as zero followed by the columns of the second query in the same fashion.

How can I correct this to have the desired output ?

Answer

Use conditional aggregation:

SELECT number,
       sum(case when day = '2019-08-09' then sales end) as sales_20190809,
       sum(case when day = '2019-08-09' then discount end) sa discount, sum(margin) as margin_20190810,
       sum(case when day = '2019-08-10' then sales end) as sales_20190809,
       sum(case when day = '2019-08-10' then discount end) sa discount, sum(margin) as margin_20190810
FROM table_a
WHERE day IN ('2019-08-09', '2019-08-10')
GROUP BY number;

If you want the numbers in different rows (which you don’t seem to), then use aggregation:

SELECT day, number, sum(sales) as sales, sum(discount) as discount, sum(margin) as margin
FROM table_a
WHERE day IN ('2019-08-09', '2019-08-10')
GROUP BY day, number