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. "Assume that we have an SQL query containing joins and a group-by. The standard way of evaluating this type of query is to first perform all the joins and then the group-by operation. However, it may be possible to perform the group-by early, that is, to push the group-by operation past one or more joins. Early grouping may reduce the query processing cost by reducing the amount of data participating in joins." So I need explanation how to do that
exercise is as follows in this case :
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.
Try this code
SELECT ip,id,ii,oo FROM
(SELECT I.point ip, I.date id, SUM(I.inc) ii FROM Income I GROUP BY I.point, I.date ) in1
LEFT JOIN
(SELECT O.point op, O.date od, SUM(O.out) oo FROM Outcome O GROUP BY O.point, O.date ) ou1
ON op=ip AND od=id
UNION
SELECT ip,id,ii,oo FROM
(SELECT I.point ip, I.date id, SUM(I.inc) ii FROM Income I GROUP BY I.point, I.date ) in1
RIGHT JOIN
(SELECT O.point op, O.date od, SUM(O.out) oo FROM Outcome O GROUP BY O.point, O.date ) ou1
ON op=ip AND od=id
Maybe someone can give it a name too. I don't even know how you call these SELECTS in parentheses ... :-/
Edit
Well, taking Luis LL's idea and combining it with "early grouping" one would get the following:
SELECT COALESCE(ip,op) point,COALESCE(id,od) date,ii inc,oo out FROM
(SELECT point ip, date id, SUM(inc) ii FROM Income GROUP BY point, date ) in1
FULL OUTER JOIN
(SELECT point op, date od, SUM(out) oo FROM Outcome GROUP BY point, date ) ou1
ON op=ip AND od=id
Maybe that will do the trick?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With