SQL GROUP BY before JOIN - sequence when querying
SELECT
Income.point, Income.date, SUM(out), SUM(inc)
FROM
Income
LEFT JOIN
Outcome ON Income.point = Outcome.point
AND Income.date = Outcome.date
GROUP BY
Income.point, Income.date
UNION
SELECT
Outcome.point, Outcome.date, SUM(out), SUM(inc)
FROM
Outcome
LEFT JOIN
Income ON Income.point = Outcome.point
AND Income.date = Outcome.date
GROUP BY
Outcome.point, Outcome.date;
I have this code what I want to do is to group by before joining. I can't
find a solution on google so will you please explain what to change in
queries to change sequence.
By the way this is exercise 30 from http://www.sql-ex.ru/, which says:
Under the assumption that the income (inc) and expenses (out) of the money
at each outlet (point) are registered any number of times a day, get a
result set with fields: outlet, date, expense, income.
Note that a single record must correspond to each outlet at each date.
Use Income and Outcome tables.
SELECT fso.point,
ReplyDeletefso.date,
fso.sum,
fsi.sum
FROM
(SELECT fp.point,
fp.date,
sum(fp.out) AS SUM
FROM
(SELECT dates.date,
dates.point,
outcome.code,
outcome.out
FROM
(SELECT date, point
FROM income
UNION SELECT date, point
FROM outcome) AS dates
LEFT JOIN outcome ON dates.date = outcome.date
AND dates.point = outcome.point) AS fp
GROUP BY fp.date, fp.point) AS fso
LEFT JOIN
(SELECT fp.point,
fp.date, SUM(fp.inc) AS SUM
FROM
(SELECT dates.date, dates.point,
income.code,
income.inc
FROM
(SELECT date, point
FROM income
UNION SELECT date, point
FROM outcome) AS dates
LEFT JOIN income ON dates.date = income.date
AND dates.point = income.point) AS fp
GROUP BY fp.date, fp.point) AS fsi ON fso.point = fsi.point
AND fso.date = fsi.date