Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL GROUP BY before JOIN - sequence when querying

Tags:

sql

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.

like image 648
Nick Avatar asked Feb 22 '26 01:02

Nick


1 Answers

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?

like image 172
Carsten Massmann Avatar answered Feb 24 '26 19:02

Carsten Massmann



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!