The problem is the following: I have a query which retrieves all the information of a teacher including his two salaries, which I have to add and show them by calendar, previously in another question they answered me that I could do the sum by groups, that is , I use SUM(tabla1+tabla2)
and do a GROUP
calendar but the numbers go way up and I don't understand why.
This is the query that correctly returns the values without grouping them:
SELECT profesores.nombre_profe ,
profesores.apellido_paterno ,
profesores.apellido_materno ,
puestos.puesto1 ,
puestos.puesto2 ,
(sueldos.sueldo1 + sueldos.sueldo2) AS total ,
calendarios.nombre_calendario
FROM sueldos
INNER JOIN profesores ON sueldos.profesores_id = profesores.id
INNER JOIN puestos ON puestos.profesores_id = profesores.id
INNER JOIN calendarios ON sueldos.calendarios_id = calendarios.id
WHERE profesores.id = 378;
In this query I do not use the SUM since it gives very exaggerated numbers and the result of this is:
It shows you the total and the calendars, what I want to do is show them grouped by calendar. That is, add up all the totals of said calendar.
This is my query where I group by calendar and it gives me large values:
SELECT profesores.nombre_profe ,
profesores.apellido_paterno ,
profesores.apellido_materno ,
puestos.puesto1 ,
puestos.puesto2 ,
SUM(sueldos.sueldo1 + sueldos.sueldo2) AS total ,
calendarios.nombre_calendario
FROM sueldos
INNER JOIN profesores ON sueldos.profesores_id = profesores.id
INNER JOIN puestos ON puestos.profesores_id = profesores.id
INNER JOIN calendarios ON sueldos.calendarios_id = calendarios.id
WHERE profesores.id = 378
GROUP BY calendarios.nombre_calendario;
Is there a way to do it without the results going too high, I really don't know the reason why the results go so high.
We can find the solution starting from this query that works correctly for you:
We can use it as an inner query in a nested query as follows:
Where the subquery (
sc
) contains all the data you need and the outer query adds it correctly.I leave you a proof of concept prepared online here:
https://www.db-fiddle.com/f/pNKqKqL1BmgXa6fY9AXTUN/0
I thought that it could be (it happened to me) a side effect of how table joins are resolved, but after spending a little more time on it I realize that this is not the case.
When you have problems grouping or adding values, you can make use of the fact
GROUP_CONCAT
that it allows you to show everything that has been grouped in an additional field.I have reproduced your first two complete dates (with the data provided in the image, if I had more real data I could have been completely sure of each query) in the following link:
https://www.db-fiddle.com/f/8RtDxQVrvSiHMu3VoThbGX/0
In which I run the following SQL query:
And the result has been:
You can check that all the data is there and copying and pasting each cell in a calculator checks that the results are correct, so I recommend that you use this query in your database to debug what is being added from plus.
Reflection:
Does what you mention happen to you when you are looking for more than one teacher or you do not put a restriction on this one in the
WHERE
? If so, the salaries of the other teachers will be added: https://www.db-fiddle.com/f/8RtDxQVrvSiHMu3VoThbGX/1In that case you should also group by the teacher. I always recommend grouping by a single field (primary key if possible) so I'll add
profesores.id
: https://www.db-fiddle.com/f/8RtDxQVrvSiHMu3VoThbGX/2Lastly, this code doesn't work in MySQL 5.7 or higher if absolutely all non-aggregate fields (with the
SUM
, for example) are not used in theGROUP BY
: https://www.db-fiddle.com/f/8RtDxQVrvSiHMu3VoThbGX/3Therefore, it may be advisable that, if you are not going to use these fields (you want the total per teacher, and not the total per teacher and type of position), do not put them in the
SELECT
.