Answer the question
In order to leave comments, you need to log in
MySQL. How to get rid of jambs when using GROUP BY + JOIN?
Second_material
database (recycling company, educational database) is given: Task:
Write a query with output data (point, date, out, inc, diff), in which one line corresponds to each item for each date. This task requires data from two tables to be collected in one result set; at the same time, the receipt and expenditure of money at the reception point on the same day must be in the same line.
Here is the content of the income table :
mysql> select * from income where _point = 7;
+-------+--------+------------+-----------+
| code | _point | _date | _inc |
+-------+--------+------------+-----------+
| 00010 | 007 | 2010-03-08 | 120000.00 |
| 00011 | 007 | 2010-03-08 | 80000.00 |
| 00012 | 007 | 2010-03-08 | 20000.00 |
| 00013 | 007 | 2010-04-15 | 90000.00 |
| 00014 | 007 | 2010-05-05 | 40000.00 |
| 00015 | 007 | 2010-06-12 | 65000.00 |
+-------+--------+------------+-----------+
mysql> select * from outcome where _point = 7;
+-------+--------+------------+-----------+
| code | _point | _date | _out |
+-------+--------+------------+-----------+
| 00010 | 007 | 2010-03-08 | 115000.00 |
| 00011 | 007 | 2010-03-08 | 77800.00 |
| 00012 | 007 | 2010-03-08 | 19900.00 |
| 00013 | 007 | 2010-04-15 | 84750.00 |
| 00014 | 007 | 2010-05-05 | 35500.00 |
| 00015 | 007 | 2010-06-12 | 61100.00 |
+-------+--------+------------+-----------+
SELECT income._point, income._date, @a:=SUM(income._inc) AS income,
@b:=SUM(outcome._out) AS outcome, @[email protected] AS diff
FROM income INNER JOIN outcome
ON (income._date = outcome._date AND income._point = outcome._point)
GROUP BY income._date, outcome._date HAVING income._point = 7;
mysql> source C:/SQL/Lab_8/queries/exercise_1.sql;
+--------+------------+-----------+-----------+------+
| _point | _date | income | outcome | diff |
+--------+------------+-----------+-----------+------+
| 007 | 2010-03-08 | 660000.00 | 638100.00 | 4500 |
| 007 | 2010-04-15 | 90000.00 | 84750.00 | 4500 |
| 007 | 2010-05-05 | 40000.00 | 35500.00 | 4500 |
| 007 | 2010-06-12 | 65000.00 | 61100.00 | 4500 |
+--------+------------+-----------+-----------+------+
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question