Error using SUM in MySQL and displaying by groups

Question

Error using SUM in MySQL and displaying by groups

Navigation

#1 by (2 votes)

2

The problem is the following: I have a query which takes all the information of a teacher including with his two salaries, which I have to add and show them by calendar, previously in another question they answered that I could make the sum by groups , that is, I use the SUM(tabla1+tabla2) and I do GROUP per calendar but the numbers go very high and I do not understand why.

This is the query that correctly throws me 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 all the totals of that calendar.

This is my query where I group by calendar and it gives me big 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;

Result:

There is some way to do it without the results going too high, I really do not know why the results are going so high.

mysql

asked by Eduardo Javier Maldonado 20.05.2017 в 02:23

source

1 answer

Ajax UpdatePanel does not work when deployed in IIS8 I try to get this query different

score 2 · Accepted Answer

We can find the solution based on this query that works correctly:

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

We can use it as an internal query in a nested query in the following way:

SELECT  nombre_profe,
        apellido_paterno,
        apellido_materno,
        puesto1,
        puesto2,
        SUM(total) total,
        nombre_calendario
FROM (
    SELECT  profesores.nombre_profe nombre_profe,
            profesores.apellido_paterno apellido_paterno,
            profesores.apellido_materno apellido_materno,
            puestos.puesto1 puesto1,
            puestos.puesto2 puesto2,
            (sueldos.sueldo1 + sueldos.sueldo2) total ,
            calendarios.nombre_calendario 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
) sc
GROUP BY sq.nombre_calendario

Where the subquery ( sc ) contains all the data you need and the external query adds it correctly.

I leave a proof of concept prepared online here:

link

I thought it could be treated (has happened) of a side effect of how the joints of tables are resolved, but after spending a little more time I realize that is not the case.

When you have problems grouping or adding values you can follow GROUP_CONCAT that allows you to show in an additional field everything you have grouped.

I have reproduced your first two complete dates (with the data provided in the image, if you had more real data you could have been completely sure of each query) in the following link:

link

In which I execute the following SQL query:

SELECT profesores.nombre_profe ,
       profesores.apellido_paterno ,
       profesores.apellido_materno ,
       puestos.puesto1 ,
       puestos.puesto2 ,
       SUM(
         sueldos.sueldo1 + sueldos.sueldo2
       ) AS total,
       GROUP_CONCAT(
         '(',
         sueldos.sueldo1,
         '+',
         sueldos.sueldo2,
         ')'
       SEPARATOR ' + ') AS suma,
       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

And the result has been:

You can check that all the data is there and by copying and pasting each cell into a calculator it checks that the results are correct, so I recommend that you use this query in your database to debug what it is is adding more.

Reflection:

Does this happen to you when you are looking for more than one teacher or do you not put a restriction on it in WHERE ? If this is the case, the salaries of the other professors will be added: link

In that case you should also group by the teacher. I always recommend grouping by a single field (primary key if possible) so I will add profesores.id : link

Finally, this code does not work in MySQL 5.7 or higher if absolutely not all non-aggregated fields are used (with SUM , for example) in GROUP BY : link

So it might be advisable that, if you are not going to use those fields (you want the total per teacher, and not the total per teacher and type of position), do not put them in SELECT .