E
E
Egorithm2016-05-22 18:59:39
MySQL
Egorithm, 2016-05-22 18:59:39

MySQL. How to get rid of jambs when using GROUP BY + JOIN?

Second_material database (recycling company, educational database) is given:
d2839952121a463e954c3a3be023b76b.pngTask:
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 |
+-------+--------+------------+-----------+

Here is the contents of the outcome table :
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 |
+-------+--------+------------+-----------+

Here is what I wrote:
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;

Having summed up the income and outcome fields by date, he also multiplied this amount by the number of duplicate records. And so in the whole table.
Also, the diff field outputs the same result in the entire table.
Here's what happened:
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 |
+--------+------------+-----------+-----------+------+

If we remove the aggregate function SUM , then everything outputs normally, except that it displays only the first record that comes across without summation.
What am I doing wrong?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question