Agrupar por fechas

[ismapolo]

Hola buenas.

Tengo una tabla con tres campos, donde se recoge la temperatura diaria de una serie de pueblos:

FECHA PUEBLO TEMP
2010-01-01 1 2.5
2010-01-01 5 1.3
2011-01-01 3 2.0
2011-01-03 4 3.1
2010-01-03 5 1.4
... ... ...

donde TEMP es la temperatura del día que señala el campo FECHA para un PUEBLO concreto.

Mi idea es hacer una consulta que tenga por resultado lo siguiente:

FECHA TEMP2010 TEMP2011
1-enero temp temp
2-enero temp temp
... ... ...

es decir, en la columna FECHA debe aparecer las fechas ordenadas de menor a mayor, pero sin indicar el año, sólo día y mes.

TEMP2010 debe recoger la media del dato de la temperatura de la fecha señalada en la comuna anterior, pero del año 2010, para un día concreto. Por ejemplo, la media de la temperatura de todos los pueblos el día 1 de enero, 2 de enero, etc.

La idea es comparar para cada día del año, las temperaturas medias de cada año del conjunto de pueblos.

Análogo para TEMP2011.

Gracias por la ayuda.

¡Vaya lío!

[jurena]

SELECT AVG(TEMP) media, YEAR(FECHA), MONTH(FECHA) mesnumero, DAY(FECHA) diames FROM tutabla GROUP BY YEAR(FECHA), MONTH(FECHA), DAY(FECHA) order by YEAR(FECHA), MONTH(FECHA), DAY(FECHA)

Luego poner el nombre en espalol del mes y el resto de la presentación deberás hacerlo con PHP.

[ismapolo]

Gracias por tu respuesta jurena, pero tu consulta no me permite comparar para un mismo día el dato de temperatura media de 2010 y 2011, ya que lo que hace es hallar la media de todos los años y la coloca en una sola columna.

Lo que necesito es que la consulta cree dos columnas, una con la media de todos los pueblos correspondiente a 2010 y otra a 2011, además de la columna FECHA señalando sólo el día y el mes (no el año).

¡Saludos!

[Ribon]

La verdad me puse a tirar código y no se si servirá, no lo probé ni nada.
te ayudará en algo? xD!

Código MySQL:

SELECT 
    DATE_FORMAT(an1.fecha,"%d-%M") AS dia_mes, 
    AVG(an1.temp) AS TEMP2010,
    AVG(an1.temp) AS TEMP2011
FROM 
    (SELECT * FROM tabla WHERE YEAR(fecha) = '2010') an1
JOIN 
    (SELECT * FROM tabla WHERE YEAR(fecha) = '2011') an2
    ON DATE_FORMAT(an1.fecha,"%d-%m") = DATE_FORMAT(an2.fecha,"%d-%m")
GROUP BY dia_mes
ORDER BY dia_mes ASC
 

Insisto fue un porro del bueno ajajaja

Saludos.

[ismapolo]

Gracias Ribon, pero la consulta no da ningún resultado.

Saludos.

[Ribon]

Quizás sea insistente pero puede ser la condición del join que me quedó mal xD!
Quisiera tener forma de hacer pruebas, tengo el interprete en la cabeza en este momento :P

Código MySQL:

SELECT 
    DATE_FORMAT(an1.fecha,"%d-%M") AS dia_mes, 
    AVG(an1.temp) AS TEMP2010,
    AVG(an1.temp) AS TEMP2011
FROM 
    (SELECT * FROM tabla WHERE YEAR(fecha) = '2010') an1
CROSS JOIN 
    (SELECT * FROM tabla WHERE YEAR(fecha) = '2011') an2
WHERE
    DATE_FORMAT(an1.fecha,"%d-%m") = DATE_FORMAT(an2.fecha,"%d-%m")
GROUP BY dia_mes
ORDER BY dia_mes ASC
 

[ismapolo]

Tampoco da ningún resultado. No da error, pero tampoco aparece nada.

[Ribon]

Me cree mi propia tabla con datos y si me da resultados o.o
quizás puse algo que nada que ver o no se pero me enlista los días junto a los promedios de temperaturas justo como dices tu :S

EDIT: En el 3er campo era an2.fecha, puse an1 xD!

Código MySQL:

SELECT 
    DATE_FORMAT(an1.fecha,"%d-%M") AS dia_mes, 
    AVG(an1.temp) AS TEMP2010,
    AVG(an2.temp) AS TEMP2011
FROM 
    (SELECT * FROM temperaturas WHERE YEAR(fecha) = '2010') an1
CROSS JOIN 
    (SELECT * FROM temperaturas WHERE YEAR(fecha) = '2011') an2
WHERE
    DATE_FORMAT(an1.fecha,"%d-%m") = DATE_FORMAT(an2.fecha,"%d-%m")
GROUP BY dia_mes
ORDER BY dia_mes ASC
 

[ismapolo]

Sí, eso es justo lo que quiero, pero no sé porqué a mí no me da ningún resultado.

¡Gracias!

[ismapolo]

Vaya impotencia que siento teniendo el resultado delante de mí y sin poder hacerla funcionar.

En mi phpmyadmin no me da ningún resultado, ni en mi ordenador ni en el servidor, aunque dice que la consulta se ha realizado con éxito.

¿Cuál puede ser la causa?

Saludos.

[Ribon]

que no tengas registros en el 2010 ni el 2011 en el mismo mes xD!
que tipo de dato pusiste las fechas? yo la tabla la cree usando DATE

Saludos...

[ismapolo]

¡Ya está todo bien, gracias!

Aunque veo que ordena el campo FECHA por orden alfabético, y no por orden cronológico.

[gnzsoloyo]

Iniciado por ismapolo

¡Ya está todo bien, gracias!

Aunque veo que ordena el campo FECHA por orden alfabético, y no por orden cronológico.

Eso es porque tienes mal definido el ORDER BY.
Usa:

Código MySQL:

...
ORDER BY YEAR(an1.fech), MONTH(an1.fecha)
 

Tip: Si el orden es ascendente, ponerle ASC es redundante.

[gerardo_goh]

Iniciado por gnzsoloyo

Eso es porque tienes mal definido el ORDER BY.
Usa:

Código MySQL:

...
ORDER BY YEAR(an1.fech), MONTH(an1.fecha)
 

Tip: Si el orden es ascendente, ponerle ASC es redundante.

No sean malos pueden mostrar el contenido de las tablas para entender ya que tengo un problema similar, muchas gracias, Saludos!!!

[Ribon]

Vaya pues cual era el problema para que no te mostrara registros??

[gnzsoloyo]

Iniciado por gerardo_goh

No sean malos pueden mostrar el contenido de las tablas para entender ya que tengo un problema similar, muchas gracias, Saludos!!!

No entendí la observación.

Simplemente decía cómo ponerlos en el orden que los necesitas.