Sql-ex Solutions

Exercise: 30

Under the assumption that receipts of money (inc) and payouts (out) can be registered any number of times a day for each collection point [i.e. the code column is the primary key], display a table with one corresponding row for each operating date of each collection point. Result set: point, date, total payout per day (out), total money intake per day (inc). Missing values are considered to be NULL.

Solution

SELECT point, date, SUM(out), SUM(inc)
FROM(
    SELECT point, date, null AS out, SUM(inc) AS inc
    FROM Income
    GROUP BY point, date
    UNION 
    SELECT point, date, SUM(out) AS out, NULL AS inc
    FROM Outcome
    GROUP BY point, date) AS InOut
GROUP BY point, date

References