I have my two tables which have their total and their date in each table, what I want to obtain is the month in a single column of both tables, but if the month is different from one another, the values are displayed in the respective rows , I attach my tables, and the result I want to get with my failed proposal.
These are my two tables
Plotting Table
+---------------+----------------+
| Fecha_OTPloteo| Total_OTPloteo |
+---------------+----------------+
| 7.00 | 2021-02-07 |
+---------------+----------------+
Technical Service Table
+------------------------+------------------------+
| Fecha_OTServicioTecnico| Total_OTServicioTecnico|
+------------------------+------------------------+
| 160.00 | 2021-02-14 |
+------------------------+------------------------+
The result I hope to get is the following
If the months are the same, they are displayed in a single column:
+------+-------------+---------+
| mes | totalPloteo | totalSt |
+------+-------------+---------+
| 2 | 7.00 | 160.00 |
+------+-------------+---------+
But if the months are different and the total does not represent that month, the following results should be displayed:
+------+-------------+---------+
| mes | totalPloteo | totalSt |
+------+-------------+---------+
| 2 | 7.00 | null |
+------+-------------+---------+
| 3 | null | 160.00 |
+------+-------------+---------+
I hope to obtain all these results through a date range, if the date is greater than... and less than... they show me the values above. I have the following code where when doing the respective where, they just don't show me the values.
code review
SELECT mes, totalPloteo, totalSt
FROM (
SELECT MONTH(Fecha_OTPloteo) mes,
SUM(Total_OTPloteo) totalPloteo
FROM ot_ploteo
WHERE Fecha_OTPloteo >= 2021 - 01 - 01
AND Fecha_OTPloteo <= 2021 - 12 - 31
GROUP BY 1
) c1
LEFT JOIN (
SELECT MONTH(Fecha_OTServicioTecnico) mes,
SUM(Total_OTServicioTecnico) totalSt
FROM ot_servicio_tecnico
WHERE Fecha_OTServicioTecnico >= 2021 - 01 - 01
AND Fecha_OTServicioTecnico <= 2021 - 12 - 31
GROUP BY 1
) c2 USING (mes)
UNION
SELECT mes, totalPloteo, totalSt
FROM (
SELECT MONTH(Fecha_OTPloteo) mes,
SUM(Total_OTPloteo) totalPloteo
FROM ot_ploteo
WHERE Fecha_OTPloteo >= 2021 - 01 - 01
AND Fecha_OTPloteo <= 2021 - 12 - 31
GROUP BY 1
) c1
RIGHT JOIN (
SELECT MONTH(Fecha_OTServicioTecnico) mes,
SUM(Total_OTServicioTecnico) totalSt
FROM ot_servicio_tecnico
WHERE Fecha_OTServicioTecnico >= 2021 - 01 - 01
AND Fecha_OTServicioTecnico <= 2021 - 12 - 31
GROUP BY 1
) c2 USING (mes);
It seems to me that the problem is with the format of the dates. Please try like this:
For this dataset:
I get this result:
Greetings and good day.
I have prepared the following; Just check that the version of MySQL that you use supports
WITH
or, failing that, I'll tell you what to do.Basically it is:
agrupador
which will be the way you group it (I use year and month here even though you indicate only month ).