Thursday, 15 August 2013

SQL GROUP BY before JOIN - sequence when querying

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.

1 comment:

  1. SELECT fso.point,
    fso.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

    ReplyDelete