Operaciones usando los ALIAS

[alyciashape]

Esto es algo que me pasa frecuentemente pero esta vez quisiera conseguir lo que deseo. Pues normalmente acabo haciéndolo o arreglándolo de otra forma.
Esta es la consulta:

Código MySQL:

SELECT columnas,
(SELECT cantidad FROM compra c WHERE c.claart=a.claart AND claemp=2 ORDER BY fecha DESC LIMIT 1) AS 'Compras Urbarrio', 
(SELECT cantidad FROM compra c WHERE c.claart=a.claart AND claemp=5 ORDER BY fecha DESC LIMIT 1) AS 'Compras Motoexpress', 
(SELECT cantidad FROM compra c WHERE c.claart=a.claart AND claemp=0 ORDER BY fecha DESC LIMIT 1) AS 'Compras Otros'
FROM `articulo` a where 1
 

Columnas es sólo que resumo que ahí hay un montón de ellas para no entorpecer el código.

El lío de parántesis es porque yo creé la vista y ahora la quise editar y me dió el código lleno de paréntesis, lo saco de aquí porque sino es un caos total. No se si volver a arreglarlo y poner otra vez el código a mano, total me imagino que me la guardará como él quiera. Tampoco se si quitar el WHERE 1 que siempre añade y no se para qué sirve.

El SQL es una vista, las 3 últimas columnas son sacadas a partir de una consulta como se puede observar. Lo que quisiera sería sumar esas 3 columnas a partir de su Alias pero no lo logro.

Se puede hacer?

[gnzsoloyo]

- No se pueden usar los alias definidos en el SELECT dentro en la misma sentencia.
- Subconsultas en un SELECT son de pésima performance.
- El AS es obsoleto, ya no se requiere más que compatibilidad para motores muy viejos. Sólo le agrega mala legibilidad.
- Es más sencillo hacer:

Código MySQL:

SELECT 
    columnas, 
    IF(claemp=2, cantidad, 0) 'Compras Urbarrio', 
    IF(claemp=5, cantidad, 0) 'Compras Motoexpress', 
    IF(claemp=0, cantidad, 0) 'Compras Otros' 
FROM `articulo` a 
ORDER BY fecha DESC
 

[alyciashape]

Muchísimas gracias gnzsoloyo eres una máquina.

No sabía que los IF daban mejor rendimiento.
El tema es que esos datos están en otra tabla por lo que añadi el campo en el FROM, supongo que esa es la forma correcta.

Código MySQL:

SELECT campos,
IF(claemp=2, cantidad, 0) 'Compras Urbarrio', 
IF(claemp=5, cantidad, 0) 'Compras Motoexpress', 
IF(claemp=0, cantidad, 0) 'Compras Otros'
FROM `articulo` a, compra c where a.claart=c.claart
GROUP BY a.claart
 

¿Pero podría sumar el total de compras de alguna forma? El 0 vendría a ser el ELSE no?

Es que poniendo SUM en otro if me suma las compras de TODA la tabla y solo me muestra un registro, claro.

También otra pega es que sólo me está sumando las compras de Urbarrio, en las otras me pone 0 siempre. Creo que es por agrupar por artículo, pero necesitaba que fuese así.

[gnzsoloyo]

Trata de no usar JOINs implícitos. En grandes cantidades de datos tienen baja performance.
Sumar agrupadamente es sencillo:

Código MySQL:

SELECT campos,
SUM(IF(claemp=2, cantidad, 0)) 'Compras Urbarrio', 
SUM(IF(claemp=5, cantidad, 0)) 'Compras Motoexpress', 
SUM(IF(claemp=0, cantidad, 0)) 'Compras Otros'
FROM `articulo` a INNER JOIN compra c ON a.claart=c.claart
GROUP BY a.claart
 

[alyciashape]

No es lo mismo hacer

FROM articulo a, compras c
WHERE a.claart=c.claart
que
articulo a INNER JOIN compra c ON a.claart=c.claart

Pensaba que si. Yo sólo usaba esa "forma" cuando es un LEFT JOIN.

Para la cantidad total lo hice así:

Código MySQL:

SUM(IF(claemp != 100, cantidad, 0)) 'Total Compras'
 

¿Lo ves correcto? Nunca será 100.

[gnzsoloyo]

El JOIN implícito lee las tablas y luego aplica el WHERE.
El INNER JOIN usa el indice de relación (si existe), y lee de él, o bien lee la primer tabla y sólo levanta los de la segunda que coincidan.
Es mucho más rápido usar INNER JOIN que la coma, aunque semánticamente sean equivalentes. El efecto en la performance se nota cuando trabajas con grandes cantidades de datos, especialmente cuando hay relaciones de foreign key definidas.

[gnzsoloyo]

Código MySQL:

SUM(IF(claemp != 100, cantidad, 0)) 'Total Compras'
 

Eso en todo caso debería ir como condición del WHERE.

[alyciashape]

Muchas gracias por todo. De verdad.

[alyciashape]

Ah, que tontería. No hace falta ni WHERE no? Con poner un simple
SUM(cantidad) AS 'Total Compras' en lugar del IF es suficiente. Solo quiero el total de compras de ese artículo.

De hecho ya lo probé y funciona. Bueno funciona de ambas maneras, pero me gusta hacer las cosas bien. Aunque a veces crees que lo haces bien y lo estás haciendo rematadamente mal.

[gnzsoloyo]

Solo quiero el total de compras de ese artículo.

Si quieres el total de compras de un determinado artículo, pones el ID del artículo en el WHERE y no agrupas a menos que haya una columna que no esté afectada por el SUM().
El total de compras, es sumar todos los que sean válidos, pero el cuál es válido es una condición a definir en el WHERE.

Postea la consulta completa que tienes y vemos los detalles.

[alyciashape]

Perdona. Si quiero eso por artículo, no para uno en concreto, sino para todos.

Esta es la consulta que me da al editar la vista. Lo modifica todo al parecer y es un caos enterarse de nada, lo peor son los paréntesis que mete. ¿Es normal?

Código MySQL:

select `a`.`claart` AS `claart`,`a`.`codigo` AS `codigo`,`a`.`nombre` AS `nombre`,`a`.`s_alm_temp_vigo` AS `s_alm_temp_vigo`,`a`.`s_perdidas` AS `s_perdidas`,`a`.`s_dot` AS `s_dot`,
(((`a`.`s_urbarrio` + `a`.`s_dresden`) + `a`.`s_consignia`) + `a`.`s_alcabre_urba`) AS `Urbarrio`,(`a`.`s_alcabre_moto` + `a`.`s_motoexpress`) AS `Motoexpress`,
(((((((((`a`.`s_alm_temp_vigo` + `a`.`s_urbarrio`) + `a`.`s_alm_temp_camion`) + `a`.`s_dresden`) + `a`.`s_consignia`) + `a`.`s_alcabre_moto`) + `a`.`s_motoexpress`) + `a`.`s_perdidas`) + `a`.`s_alcabre_urba`) + `a`.`s_dot`) AS `Total`,
sum(if((`c`.`claemp` = 2),`c`.`cantidad`,0)) AS `Compras Urbarrio`,
sum(if((`c`.`claemp` = 5),`c`.`cantidad`,0)) AS `Compras Motoexpress`,sum(if((`c`.`claemp` = 0),`c`.`cantidad`,0)) AS `Compras Otros`,
sum(`c`.`cantidad`) AS `Total Compras` 
from (`articulo` `a` join `compra` `c` on((`a`.`claart` = `c`.`claart`))) 
group by `a`.`claart`
 

Tras tirarme un buen rato arreglándolo así es como la puse yo en realidad:

Código MySQL:

SELECT a.claart, a.codigo, a.nombre, a.s_alm_temp_vigo, a.s_perdidas, a.s_dot, (a.s_urbarrio+a.s_dresden+a.s_consignia+a.s_alcabre_urba) 'Urbarrio', (a.s_alcabre_moto+a.s_motoexpress) 'Motoexpress', (a.s_alm_temp_vigo+a.s_urbarrio+a.s_alm_temp_camion+a.s_dresden+a.s_consignia+a.s_alcabre_moto+a.s_motoexpress+a.s_perdidas+a.s_alcabre_urba+a.s_dot) 'Total', 
SUM(IF(claemp=2, cantidad, 0)) 'Compras Urbarrio', 
SUM(IF(claemp=5, cantidad, 0)) 'Compras Motoexpress', 
SUM(IF(claemp=0, cantidad, 0)) 'Compras Otros',
SUM(cantidad) 'Compras Total',
FORMAT(AVG(c.media), 2) 'Media'
FROM articulo a INNER JOIN compra c ON (a.claart=c.claart) 
WHERE c.fecha = MAX(c.fecha)
GROUP BY a.claart
 

Exceptuando que ahora puse el campo media y el WHERE con la fecha. Algo que antes no me daba cuenta pero está mal.

El tema es que yo en la tabla compras, ejecutando un script, calculo todos los campos a fecha actual (en un DATETIME) y guardo en la tabla compra con la fecha y datos por artículo y por empresa.

Es decir que en compras por cada artículo tengo 3 registros que quiero recoger, que además serían los que tengan la última fecha. Todos tienen la misma. En el citado script capto la fecha y la uso para todos los insert así que es idéntica para todos los que quiero recoger. Por ejemplo:

Si paso el script ahora me guardaria en la fecha
2012-12-19 10:07:20 3 registros por cada artículo guardando las compras y otros datos y a que empresa pertenecen.

Tal y como tengo la consulta no funciona, me dice que el group by ahí no tiene cabida. Sin el WHERE funciona pero claro, me coge todas las fechas.

Además todavía me queda hacer otra tabla para las ventas y puede que una más para movimientos. Espero que tenga bien planteado el sistema.

De momento lo solucioné así:

Código MySQL:

WHERE c.fecha = (SELECT MAX(fecha) FROM compra)
GROUP BY a.claart
 

Pero por lo que me decías prefería evitar subconsultas.

Además, pensando en más adelante, cuando añada la tabla venta que tendrá otra fecha diferente no se si me servirá. Son tablas donde meto datos por scripts y tardan mucho en realizarse pues hay muchos datos, así que tendrán fechas diferentes si o si. No se si con un compra.fecha = (SELECT MAX(fecha) FROM compra) y un venta.fecha = (SELECT MAX(fecha) FROM venta) funcionaría como espero.


Una cosa más, perdona por marearte tanto, ahora que añadí ventas el tiempo de ejecución pasa de 1 segundo de antes a 40 segundos, porque añadí una nueva tabla. Sospecho que esta no es la manera correcta de realizar el INNER JOIN con 3 tablas teniendo una en común.

Código MySQL:

SELECT a.claart, a.codigo, a.nombre, a.s_alm_temp_vigo, a.s_perdidas, a.s_dot, (a.s_urbarrio+a.s_dresden+a.s_consignia+a.s_alcabre_urba) 'Urbarrio', (a.s_alcabre_moto+a.s_motoexpress) 'Motoexpress', (a.s_alm_temp_vigo+a.s_urbarrio+a.s_alm_temp_camion+a.s_dresden+a.s_consignia+a.s_alcabre_moto+a.s_motoexpress+a.s_perdidas+a.s_alcabre_urba+a.s_dot) 'Total', 
SUM(IF(c.claemp=2, c.cantidad, 0)) 'Compras Urbarrio', 
SUM(IF(c.claemp=5, c.cantidad, 0)) 'Compras Motoexpress', 
SUM(IF(c.claemp=0, c.cantidad, 0)) 'Compras Otros',
SUM(c.cantidad) 'Compras Total',
FORMAT(AVG(c.media), 2) 'MediaC',
SUM(IF(v.claemp=2, v.cantidad, 0)) 'Ventas Urbarrio', 
SUM(IF(v.claemp=5, v.cantidad, 0)) 'Ventas Motoexpress', 
SUM(IF(v.claemp=0, v.cantidad, 0)) 'Ventas Otros',
SUM(v.cantidad) 'Ventas Total',
FORMAT(AVG(v.media), 2) 'MediaV'
FROM venta v INNER JOIN  articulo a ON (a.claart=v.claart) 
INNER JOIN compra c ON (a.claart=c.claart)
WHERE c.fecha = (SELECT MAX(fecha) FROM compra)
GROUP BY a.claart
 

He probado con USING en lugar de ON, con WHERE campo=campo2 y nada. No bajo de los 40 segundos.

¿Podría tener que ver conque claart es la clave primaria de articulo pero no es ni primaria ni foránea en las otras tablas?

Otra edición:
Añadí índices a los claart y de los 40 segundos que tardaba antes ahora tarda 0,07 segundos. Vaya cambio!
Sin embargo desde entonces me aparece aleatoriamente este error:

Código error:

Fatal error: Maximum execution time of 30 seconds exceeded in C:\xampp_new\phpmyadmin\libraries\session.inc.php on line 96
 

Quité los índices que había creado y también la vista y me sigue dando ese error. Reinicié apache y mysql y sigue igual.

Cuanto se aprende con estas bases de datos grandecitas. Y eso que esta no es demasiado grande.