SUM o GROUP BY?

DooBie · #1 (**permalink**) 05/10/2013, 08:27

Hola chumachos, tengo las siguientes tablas
tabla resultados
-id
-carrera
-puntos
-corredor
-posicion

tabla corredores
-id
-corredor

tabla carreras
-id
-carrera
-fecha

Bien, la tabla carreras y corredores son faciles, guarda los corredores y carreras con sus fechas, en la tabla resultados guardo relacion con las tablas corredores y carreras y ademas guardo los puntos de ese corredor en esa carrera en concreto.
Por ejemplo, tabla resultados (una muestra de lo que tengo en la bd):

Código MySQL:

Ver original+-----------+-----------+---------------+--------------+-----------------+
| result_id | result_gp | result_points | result_rider | result_position |
+-----------+-----------+---------------+--------------+-----------------+
|         1 |         1 |            25 |           32 |               1 |
|         2 |         1 |            20 |           20 |               2 |
|         3 |         1 |            16 |           31 |               3 |
|         4 |         1 |            13 |           14 |               4 |
|         5 |         1 |            11 |           16 |               5 |
|         6 |         1 |            10 |           11 |               6 |
|         7 |         1 |             9 |            1 |               7 |
|         8 |         1 |             8 |           26 |               8 |
|         9 |         1 |             7 |           15 |               9 |
|        10 |         1 |             6 |            7 |              10 |
|        11 |         1 |             5 |           18 |              11 |
|        12 |         1 |             4 |            8 |              12 |
|        13 |         1 |             3 |            5 |              13 |
|        14 |         1 |             2 |           25 |              14 |
|        15 |         1 |             1 |            4 |              15 |
|        16 |         1 |             0 |           28 |              16 |
|        17 |         1 |             0 |           27 |              17 |
|        18 |         1 |             0 |           23 |              18 |
|        19 |         1 |             0 |            2 |              19 |
|        20 |         1 |             0 |            6 |              20 |
|        21 |         1 |             0 |            3 |              21 |
|        22 |         1 |             0 |           17 |              22 |
|        23 |         1 |             0 |           24 |              23 |
|        24 |         1 |             0 |           10 |              24 |
|        25 |         2 |            25 |           31 |               1 |
|        26 |         2 |            20 |           14 |               2 |
|        27 |         2 |            16 |           32 |               3 |
|        28 |         2 |            13 |           16 |               4 |
|        29 |         2 |            11 |            3 |               5 |
|        30 |         2 |            10 |           20 |               6 |
|        31 |         2 |             9 |            1 |               7 |
|        32 |         2 |             8 |           11 |               8 |
|        33 |         2 |             7 |           26 |               9 |
|        34 |         2 |             6 |           15 |              10 |
|        35 |         2 |             5 |           18 |              11 |
|        36 |         2 |             4 |           17 |              12 |
|        37 |         2 |             3 |            7 |              13 |
|        38 |         2 |             2 |            8 |              14 |
|        39 |         2 |             1 |           25 |              15 |
|        40 |         2 |             0 |           27 |              16 |
|        41 |         2 |             0 |            4 |              17 |
|        42 |         2 |             0 |            5 |              18 |
|        43 |         2 |             0 |           28 |              19 |
|        44 |         2 |             0 |           24 |              20 |
|        45 |         2 |             0 |           30 |              21 |
|        46 |         2 |             0 |            6 |              22 |
|        47 |         2 |             0 |           23 |              23 |
|        48 |         2 |             0 |            2 |              24 |
|        49 |         3 |            25 |           14 |               1 |
|        50 |         3 |            20 |           31 |               2 |
+-----------+-----------+---------------+--------------+-----------------+

Dados esos datos, quiero hacer una consulta que me devuelva todos los registros (los JOIN ya los se hacer) pero que me devuelva un campo puntos, en el que me sume los puntos por corredor, tengo esta consulta ya hecho (como vereis, la bd es mas extensa, pero no influye en lo que quiero hacer)

Código MySQL:

Ver originalSELECT tbl_result.result_points, tbl_rider.rider_name, tbl_gp.gp_name, tbl_result.result_position, DATE_FORMAT(tbl_gp.gp_date, '%d/%m/%Y') gp_date, tbl_rider.rider_number, tbl_gp.gp_id, SUM(tbl_result.result_points) points
        FROM tbl_result
        INNER JOIN tbl_rider ON tbl_rider.rider_id = tbl_result.result_rider
        INNER JOIN tbl_category ON tbl_category.category_id = tbl_rider.rider_category
        INNER JOIN tbl_gp ON tbl_gp.gp_id = tbl_result.result_gp
        WHERE YEAR(tbl_gp.gp_date) = 2013 AND tbl_category.category_id = 1
        ORDER BY points DESC, tbl_gp.gp_date ASC, tbl_result.result_points DESC, tbl_result.result_position ASC
        LIMIT 5

Si uso el SUM(tbl_result.result_points) me devuelve un unico resistro, el que corresponde al id = 1 con la suma de TODOS los puntos de todas las carreras y todos los corredores, lo que quiero es que me devuelva por separado por corredor y carrera, espero que se entienda....
Como podría hacerlo?

gnzsoloyo · #2 (**permalink**) 05/10/2013, 09:12

Cita:

Si uso el SUM(tbl_result.result_points) me devuelve un unico resistro,

Por supuesto que te devolverá sólo uno, si no agrupas de alguna forma. Precisamente por eso tanto SUM(), como AVG(), MAX(), MIN() y otras son lo que se denomina "funciones agrupadas", "de agrupamiento" o "modificadoras de GRoUP BY".
De hecho, no puedes usarla sobre una columna si en el SELECT estás invocando más de una columna no afectada por funciones.
Es decir, es obligatorio el uso de GROUP BY en condiciones como:

Código MySQL:

Ver originalSELECT a, b, SUM(c)
FROM...

La única distinción que existen referida a MySQL es que este DBMS admite que no agrupe por todas las columnas no afectadas, y además permite hacer agrupamientos sobre columnas no listadas en el SELECT:

Código MySQL:

Ver originalSELECT a, b, SUM(c)
FROM...
...
GROUP BY a

Código MySQL:

Ver originalSELECT a, b, SUM(c)
FROM...
...
GROUP BY d

Pero hay que tener cuidado con eso, porque en esos casos MySQL devolverá, en la columna donde no se aplique función y se indique en el GROUP BY el valor del primer registro que encuentre que cumpla con el GROUP BY.
¿Se entiende esto último?

Eso puede causar respuestas inesperadas en ciertos casos.

DooBie · #3 (**permalink**) 05/10/2013, 15:28

Gracias por contestar.
Lo he entendido, aunque lo que pretendía hacer fallaba en cuanto a concepto, ya que no se puede conseguir con una consulta.
Probando con group by hace algo parecido, así que en la lógica del programa lo adaptaré a lo que quiero.

Saludos!

gnzsoloyo · #4 (**permalink**) 05/10/2013, 16:06

Mira, por lo poco que se entiende tu consulta, entraña ciertos riesgos de datos inconsistentes, a causa de posibles relaciones de cardinalidad múltiple.
Me explico.
Tu consulta es esta (aplicando alias para limpiar el codigo, te lo recomiendo):

Código MySQL:

Ver originalSELECT 
    re.result_points, 
    ri.rider_name, 
    gp.gp_name, 
    re.result_position, 
    DATE_FORMAT(gp.gp_date, '%d/%m/%Y') gp_date, 
    ri.rider_number, 
    gp.gp_id, 
    SUM(re.result_points) points
FROM tbl_result re
    INNER JOIN tbl_rider ri ON re.result_rider = ri.rider_id 
    INNER JOIN tbl_category c ON c.category_id = ri.rider_category
    INNER JOIN tbl_gp gp ON gp.gp_id = re.result_gp
WHERE 
    YEAR(gp.gp_date) = 2013 
    AND c.category_id = 1
ORDER BY 
    points DESC, 
    gp.gp_date ASC, 
    re.result_points DESC, 
    re.result_position ASC
LIMIT 5

Hay dos situaciones que pueden generar problemas:
1) Si al menos uno de los INNER JOIN se está aplicando a una relación 1:N o bien N:N, el valor de result_points se repetirá N veces y se producirán sumatorias incorrectas.
2) El LIMIT 5 se resuelve en MySQL devolviendo los 5 primeros valores simples encontrados, sin importar si corresponden realmente a la posición que ocupan. Es decir, puede dar resultados erroneos.
Creo que para que devuelva los resultados que esperas (y que pareciera posible), la consulta no es tan simple como la has escrito. A mi entender, puede requerir cierta elaboración... pero no nos estás brindando los datos necesarios para poder aconsejarte.

DooBie · #5 (**permalink**) 05/10/2013, 16:30

Gracias de nuevo gnzsoloyo, vamos por partes :p
En cuanto al limit, tienes razón, en este caso sobra.
Los alias los suelo usar, pero una vez ya tengo la consulta clara del todo, vamos una vez he depurado el código, como lo tengo ahora lo veo mas claro para ir haciendo pruebas.
En cuanto a lo de dar mas información, pues, aquí la traigo :)
Estas son las tablas que tengo:

Código MySQL:

Ver originalmysql> describe tbl_rider;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| rider_id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| rider_name     | varchar(150) | NO   |     | NULL    |                |
| rider_number   | int(11)      | NO   |     | NULL    |                |
| rider_category | int(11)      | NO   | MUL | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
 
mysql> describe tbl_gp;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| gp_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| gp_name | varchar(150) | NO   |     | NULL    |                |
| gp_date | date         | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> describe tbl_category;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| category_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| category_name | varchar(30) | NO   |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
 
mysql> describe tbl_result;
+-----------------+---------+------+-----+---------+----------------+
| Field           | Type    | Null | Key | Default | Extra          |
+-----------------+---------+------+-----+---------+----------------+
| result_id       | int(11) | NO   | PRI | NULL    | auto_increment |
| result_gp       | int(11) | NO   | MUL | NULL    |                |
| result_points   | int(11) | NO   |     | NULL    |                |
| result_rider    | int(11) | NO   | MUL | NULL    |                |
| result_position | int(11) | NO   |     | NULL    |                |
+-----------------+---------+------+-----+---------+----------------+

Y estas las relaciones:

Código MySQL:

Ver original+--------------------------+--------------------------+
| foreign key              | references               |
+--------------------------+--------------------------+
| tbl_result.result_gp     | tbl_gp.gp_id             |
| tbl_result.result_rider  | tbl_rider.rider_id       |
| tbl_rider.rider_category | tbl_category.category_id |
+--------------------------+--------------------------+

Todas las relaciones son 1:N, con lo que me preocupa lo que comentas de datos repetidos y el posible error a la hora de calcular alguna sumatoria.

Bien, dada toda esta información, y conociendo parte de los datos que contiene la tabla, esto es sólo un fragmento:

Código MySQL:

Ver original+-----------+-----------+---------------+--------------+-----------------+
| result_id | result_gp | result_points | result_rider | result_position |
+-----------+-----------+---------------+--------------+-----------------+
|         1 |         1 |            25 |           32 |               1 |
|         2 |         1 |            20 |           20 |               2 |
|         3 |         1 |            16 |           31 |               3 |
|         4 |         1 |            13 |           14 |               4 |
|         5 |         1 |            11 |           16 |               5 |
|         6 |         1 |            10 |           11 |               6 |
|         7 |         1 |             9 |            1 |               7 |
|         8 |         1 |             8 |           26 |               8 |
|         9 |         1 |             7 |           15 |               9 |
|        10 |         1 |             6 |            7 |              10 |
|        11 |         1 |             5 |           18 |              11 |
|        12 |         1 |             4 |            8 |              12 |
|        13 |         1 |             3 |            5 |              13 |
|        14 |         1 |             2 |           25 |              14 |
|        15 |         1 |             1 |            4 |              15 |
|        16 |         1 |             0 |           28 |              16 |
|        17 |         1 |             0 |           27 |              17 |
|        18 |         1 |             0 |           23 |              18 |
|        19 |         1 |             0 |            2 |              19 |
|        20 |         1 |             0 |            6 |              20 |
|        21 |         1 |             0 |            3 |              21 |
|        22 |         1 |             0 |           17 |              22 |
|        23 |         1 |             0 |           24 |              23 |
|        24 |         1 |             0 |           10 |              24 |
|        25 |         2 |            25 |           31 |               1 |
|        26 |         2 |            20 |           14 |               2 |
|        27 |         2 |            16 |           32 |               3 |
|        28 |         2 |            13 |           16 |               4 |
|        29 |         2 |            11 |            3 |               5 |
|        30 |         2 |            10 |           20 |               6 |
|        31 |         2 |             9 |            1 |               7 |
|        32 |         2 |             8 |           11 |               8 |
|        33 |         2 |             7 |           26 |               9 |
|        34 |         2 |             6 |           15 |              10 |
|        35 |         2 |             5 |           18 |              11 |
|        36 |         2 |             4 |           17 |              12 |
|        37 |         2 |             3 |            7 |              13 |
|        38 |         2 |             2 |            8 |              14 |
|        39 |         2 |             1 |           25 |              15 |
|        40 |         2 |             0 |           27 |              16 |
|        41 |         2 |             0 |            4 |              17 |
|        42 |         2 |             0 |            5 |              18 |
|        43 |         2 |             0 |           28 |              19 |
|        44 |         2 |             0 |           24 |              20 |
|        45 |         2 |             0 |           30 |              21 |
|        46 |         2 |             0 |            6 |              22 |
|        47 |         2 |             0 |           23 |              23 |
|        48 |         2 |             0 |            2 |              24 |
|        49 |         3 |            25 |           14 |               1 |
|        50 |         3 |            20 |           31 |               2 |
+-----------+-----------+---------------+--------------+-----------------+

Esto es lo que quiero conseguir, recuperar todos los registros dado un año (a filtrar en la tabla tbl_gp.gp_date) y categoia concreta (a filtrar en tbl_category.category_id) en la que obtenga las relaciones de las tablas, junto con los campos tbl_result.result_points, tbl_rider.rider_name, tbl_gp.gp_name, tbl_result.result_position, DATE_FORMAT(tbl_gp.gp_date, '%d/%m/%Y') gp_date, tbl_rider.rider_number, tbl_gp.gp_id, SUM(tbl_result.result_points) points, PERO necesito que el campo points este agrupado por tbl_rider.rider_number, OJO, que ahora viene lo especial, hasta aquí consigo que me devuelva todo bien, pero al agrupar por tbl_rider.rider_number solo me devuelve un registro por rider, y lo que necesito son TODOS los registros dados el año (tbl_gp.gp_date) y la categoría (tbl_category.category_id)

Espero que ahora pueda entenderse un poco mas, aunque creo, que de una sola consulta no puede hacerse lo que pido, por el simple hecho de hacer un GROUP BY pero ya no estoy seguro, de ahí que haya preguntado :p

La posible solución que veo, es hacer una primera consulta recuperando el total de puntos con SUM y agrupando por rider_number, y una segunda consulta, como la que tenía desde un principio. Luego en mi script, hacer un bucle para adaptarlo a mis necesidades, que eso ya sería fuera de este subforo.

Ahí queda eso, bufff... cuanta letra junta!
Gracias, aunque solo sea por haber leído todo esto.