Contar y agrupar datos de una tabla

[chelodelsur]

Hola a todos, desde ya gracias por la ayuda que me han estado dando al leer este foro, soy un novato en My Sql, y tengo la siguiente pregunta,

Necesito contar de una tabla la cantidad de registros ingresados y a su vez agruparlos por día, mes y año.

Ya he leido varios post donde se trata como contar los registros entre fechas etc. mi problema es como a partir del campo fecha, obtener el día (lunes, martes,...), el mes (enero, febrero,..) y el año (2007, 2008, ..)

tengo la siguiente consulta que me devuelve la cantidad de registros.

SELECT COUNT(log_0.row_id) FROM mitabla.log log_0 WHERE fecha>='01/04/2008' AND fecha<'14/5/2008';

lo que necesito es que me entrege un resumen de

año....... día ..............total row_id
2008...... lunes............123
2008...... martes..........234 (los puntos es solo para separar las columnas)

Agradeceré su ayuda

[quimfv]

Tienes weekday() y en el manual de mysql toda las funciones relacionadas con las fechas

WEEKDAY(date)

Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
-> 1
mysql> SELECT WEEKDAY('1997-11-05');
-> 2

Código:

SELECT Year(log_0.fecha) as `año`, WEEKDAY(log_0.fecha) as `Día`, COUNT(*) FROM mitabla.log log_0 
group by Year(log_0.fecha), WEEKDAY(log_0.fecha)
HAVING log_0.fecha>='01/04/2008' AND log_0.fecha<'14/5/2008';

Quim

[chelodelsur]

Muchas gracias Quim por la respuesta.

Sin embargo ahora ha aparecido un error que no logro sacar

"sql ejecución de error # 1054, Respuesta de la base de datos:
Unknown column 'log_0.fecha ' in 'having clause' "

Como ya dije soy totalmente novato

Gracias y espero me puedan dar una mano.

[gnzsoloyo]

El campo log_0.fecha no existe o no pertenece a l atabla.

[quimfv]

Postea la sentencia que usas con los nombres reales, yo he puesto siguiendo tu ejemplo mitabla.log log_0 no se si ese es el nombre real... log_0 seria el alias....

Quim

[chelodelsur]

Mmm, ese campo si existe en la tabla log,

de hecho tengo otras consultas hechas y me funcionan sin problema. Mira ahora varió un poco el texto del error, lo unico que hice fue cambiar "fecha" por "Fecha"

Unknown column 'log_o.fecha' in 'having clause'e manual that corresponds to your MySql server version for the right syntax to use near

SELECT Year(log_0.fecha) as `año`, WEEKDAY(log_0.fecha) as `Día`, COUNT(*) FROM IVR.log log_0
group by Year(log_0.fecha), WEEKDAY(log_0.fecha)
HAVING log_0.fecha>='01/04/2008' AND log_0.fecha<'14/5/2008';

La base: se llama IVR
La tabla se llama log
Los campos involucrados: fecha

espero haya sido claro para faciliar la ayuda

desde ya gracias.

[gnzsoloyo]

No estás cumpliendo con el estándar SQL cuando usas el HAVING junto a >, <, >=, <=. Esoso operadores y esa comparación deben ir en el WHERE.
Pruebalo así:

Código:

SELECT Year(log_0.fecha) as `año`, WEEKDAY(log_0.fecha) as `Día`, COUNT(*) 
FROM IVR.log log_0
WHERE log_0.fecha>='01/04/2008' AND log_0.fecha<'14/5/2008'
group by Year(log_0.fecha), WEEKDAY(log_0.fecha);

[chelodelsur]

Muchas gracias a todos los que me ha ayudado.

El último código resultó, hice la consulta y me devuelve datos, pero.... ya parece cuento. por favor no se aburran

Este es el código que ejecuté

SELECT Year(log_0.fecha) as `año`, MONTHname(log_0.fecha) as `Mes` , DAYNAME(log_0.fecha) as `Día`, COUNT(log_0.row_id)
FROM IVR.log log_0
WHERE log_0.fecha>='12/05/2008' AND log_0.fecha<'15/5/2008'
group by Year(log_0.fecha),MONTHname(log_0.fecha), DAYNAME(log_0.fecha);

Pero en la columna año, me devuelve valores 2012, 2013, 2014, 2015,
No se si es un problema de formato del campo fecha, el formato actual es chard (10)

UNA VEZ MUCHAS GRACIAS

[jurena]

Ese es el problema.
Pero para resolverlo debes vigilar cómo has metido los datos de fecha en la base de datos. Si lo has hecho en este orden año/mes/día, ej., 2008-04-30, que es el que acepta MySQL, te bastará con cambiar la naturaleza del campo fecha a DATE. Si el orden es distinto, tendrás que usar SUBSTR() para localizar cada parte y reunirlas en el nuevo campo de fecha que crearás como DATE. Dinos cómo lo tienes e intentaremos ayudarte...

[chelodelsur]

Hola Jurema

Efectivamente el campo viene dd/mm/aaaa y tratando de cambiar el formato casi me hecho la bd,

ahora entonces como se usa SUBSTR() ???

Saludos y gracias a todos

[jurena]

Tienes dos opciones (según tu atrevimiento a la hora de realizar los cambios), pero lo primero es comprobar el modo en que has metido los datos. Si has usado siempre dos dígitos para día y mes y cuatro para el año, y tienes el orden dd-mm-aaaa, es decir, día, mes y año separados por / o -, podrás hacer dos cosas (por ej. dos de enero de 2004, 02-01-2004). Además, el campo donde está guardado el dato en MySQL, por lo que me dices, es VARCHAR.
Primer método: crear un nuevo campo en MysQL tipo DATE y al que yo he llamado fechanueva; al campo donde tienes la fecha ahora y es VARCHAR lo he llamado fechamal.
UPDATE tutabla SET fechanueva = CONCAT(SUBSTR(fechamal,7,4), '-', SUBSTR(fechamal, 4,2), '-', SUBSTR(fechamal, 1,2))

Luego revisas que las fechas en fechanueva aparecen bien y posteriormente eliminas el campo fechamal y después cambias el nombre del campo fechanueva a fechamal (no sé como lo llamabas tú).

El segundo método es más directo, pero debes vigilar que los datos están metidos como me has dicho. En realidad haces el cambio sobre el mismo campo fechamal y luego cambias su tipo a DATE.

UPDATE tutabla set fechamal = REPLACE (fechamal,fechamal, CONCAT(SUBSTR(fechamal,7,4), '-', SUBSTR(fechamal, 4,2), '-', SUBSTR(fechamal, 1,2)))

compruebas que los cambios se han producido y después cambias el tipo del campo de VARCHAR a DATE

De todas formas, para tu tranquilidad hazlo primero sobre un solo registro (por eje. el que tenga id = 3, añadiendo WHERE id = 3 en las consultas]) o sobre una copia de la tabla o de la base. Cuando veas que funciona bien, hazlo en la base empleando el método que más te guste y quitando ese where id = 3 para que afecte a todos los registros.

[chelodelsur]

Una vez más gracias.

te cuento la tabla es alimentada por una aplicación de telefonía que me entrega en este formato los datos. Así que voy a evaluar el mejor camino para darle una solución.
Si logro modificar el formato en la aplicación que entrega la información, solo tendré que hacer eso y luego cambiar los datos ya ingresados hacia atráz, ahora si no es posible cambiar el formato de alimentación tendré que pensar en una tabla espejo, u otra columna que pueda actualizar cuando necesite el reporte, etc.

No se si tienes ideas para este posible escenario soy todo oídos.

Gracias

[chelodelsur]

Muchas gracias a todos los que ha colaborado,

Finalmente este es el código que quedó, creé un campo "date" de formato date, con el que saco la consulta,

SELECT Year(log_0.date) as `año`, MONTHNAME(log_0.date) as `Mes` , dayname(log_0.date) as `Día`, COUNT(log_0.empresa_sele)
FROM IVR.log log_0
WHERE log_0.date>='2008-05-12' AND log_0.date<='2008-05-18'
group by Year(log_0.date),MONTHname(log_0.date), DAYNAME(log_0.date)
ORDER BY `Día` ASC LIMIT 1,100;

como les comentaba anteriormente el campo "fecha" de formato varchar, es alimentado por otra aplicación y sobre la que no puedo modificar su extructura, así las cosas por el momento lo que hago es un UPDATE al campo "date" para luego ejecutar la consulta.

Si alguien tiene una mejor forma de hacer este update soy todo oído

Gracias

[chelodelsur]

Y como sería agrupar las dos intrucciones en una sola??

o sea que primero ejecute el llenado de la columna "date" mediante UPDATE y luego haga la consulta de registros por día???

UPDATE `IVR`.`log` SET `date` = concat(substring(fecha,7,4),"/",substring(fecha,4,2),"/", substring(id_llamada,1,2))
WHERE `date`='<null>';
la sentencia deviera detectar los registros vacios del campo "date" y llenarlos con los valores extraidos y modificados desde el campo "fecha" no se si en where es correcto el valor null para identificar los registros vacíos.
Luego de llenado los registros hay que ejecutar la consulta.

SELECT Year(log_0.date) as `año`, MONTHNAME(log_0.date) as `Mes` , dayname(log_0.date) as `Día`, COUNT(log_0.row_id)
FROM IVR.log log_0
WHERE log_0.date>='2008-05-12' AND log_0.date<='2008-05-18'
group by Year(log_0.date),MONTHname(log_0.date), DAYNAME(log_0.date)
ORDER BY `Día` ASC LIMIT 1,100;

Como quedaría el código para ejecutar de una toda la orden??

Muchas gracias desde ya

[jurena]

chelodelsur,

Esto que has puesto es PHP, y deberías, en todo caso, preguntarlo en ese foro. De todas formas, yo creo que el problema es otro y corresponde a la base de datos. La idea es que cuando te cargue un dato, rellene el campo de fecha automáticamente. Para eso deberías usar un disparador (trigger) o tal vez un procedimiento almacenado (store procedure). El problema puede ser tal vez la versión de MySQL. Mira cuál usas y abre un nuevo post en MySQL titulado trigger para actualizar o insertar un dato... Yo no trabajo con triggers, pero sí otra gente del foro MysQL, y podrán ayudarte con una sintaxis en la que al insertar nuevos registros te actualice el campo que has puesto como fecha con el dato de fecha en orden. De ese modo no tendrías que preocuparte, pues nada más insertarlo lo tendrías.
Creo que esa no es mala solución.

[chelodelsur]

Gracias jurena:

Apelando a la buena voluntad de todos ¿Alguien tiene alguna propuesta para esta consulta??

Desde ya gracias.

[jurena]

Chelodelsur,

Si no has recibido ayuda en el foro PHP ni en este de MYSQL, y sigues sin resolver el tema, te recomiendo que hagas lo que te propuse. Una vez averiguado si los datos te los cargan mediante INSERT (imagino que así será), abres un nuevo post pidiendo ayuda para hacer un trigger que al insertar o actualizar un campo, inserte
concat(substring(dato,7,4),"/",substring(dato,4,2),"/", substring(dato,1,2))
de ese campo en otro campo. Yo he hecho pruebas cargando otros datos; la verdad es que he tenido problemas con el concat, pero lo del trigger o un trigger que llame a un procedimiento almacenado será tu solución, y además muy cómoda, pues nada más cargarte los datos, los tendrás actualizados sin tener que hacer nada, salvo crear ese trigger para tu tabla.
Si quieres mi consejo, pídelo a nuestros amigos del foro; seguro que alguien tiene experiencia y puede ayudarte. Pero antes mira qué versión de MySQL utilizas, pues creo que es a partir de 5.0.2 que puedes utilizar los triggers.

Suerte

[chelodelsur]

Gracias por las respuestas Jurena.

Lamentablemente mi BD es versión 4.0.13 lo que no me permite hacer trigger, sin embargo estoy explorando otras soluciones. me vpy a aclarar bien y luego posteo mis preguntas

Una vez más gracias a todos por su ayuda

[gnzsoloyo]

¿Por qué no puedes actualizar a 5.0?

[chelodelsur]

Es la solución, obviamente,

Sin embargo esta base es alimentada por una aplicación de telefonía que no he logrado (por el proveedor) que me indique si trabaja con la nueva versión. Si es posible que trabaje con 5.0 genial, si no hbrá que ingeniarselas.

Muchas gracias a todo, y en cuanto tenga novedades las posteo.

[chelodelsur]

Hola a todos:

Tengo una nueva consulta, la he puesto en este mismo hilo porque a pesar de ser otra pregunta es sobre el mismo tema,

Tengo la siguiente consulta, que me devuelve 4 columnas , año, mes tipo mensaje y count, el tema es que necesito que además me sume los valores de la columna count, pero solo los que en la columna tipo mensaje no estan vacios.

SELECT Year(log_0.fecha_date) as `año`, MONTHNAME(log_0.fecha_date) as `Mes` , t_mensaje as `Tipo_Mensaje` ,
COUNT(*) FROM ivr.log log_0
WHERE log_0.fecha_date>='2008-04-01'
AND log_0.fecha_date<='2008-04-31'
group by Year(log_0.fecha_date),MONTHname(log_0.fecha_date) , t_mensaje

año mes tipo mensaje count
2008 mayo '' 1000
2008 mayo saludo 2000
2008 mayo despedida 2000


Entonces necesito un campo en donde me diga que la suma de saludo + despedida es 4000.

Espero se entienda, bueno y si no me dicen y trato de explicarlo mejor

desde ya gracias por su buena voluntad

[jurena]

tal vez añadiendo esto en el SELECT

SUM (IF(t_mensaje IS NULL, 0, 1)) AS mensajesconcontenido

[chelodelsur]

Gracias Jurena por tu siempre buena voluntad

Esta alternativa ya la intente, tal vez no la ubique correctamente.
¿como iría en el codigo que posté?

Desde ya otra vez gracias

[jurena]

llevas razón. No me percaté de que ya tenías agrupado por el tipo de mensaje.
Si lo que quieres es ese resultado yo lo haría así (adapta los campos y los datos de comparación a los tuyos)

SELECT YEAR(fecha) AS año, MONTHNAME (fecha) AS mes, SUM (IF(t_mensaje='saludo', 1,0)) AS saludo, SUM (IF(tipo_mensaje='despedida', 1,0)) AS despedida, SUM (IF(t_mensaje='saludo' OR t_mensaje='despedida', 1,0)) AS saludodespedida, SUM (IF(t_mensaje='', 1,0)) AS sinmensaje FROM tutabla GROUP BY YEAR (fecha), MONTH(fecha)

pero esto lo haces sin agrupar por t_mensaje. La consulta mediante WHERE puedes usarla, pero no el COUNT, pues ahora no agrupas por el contenido del campo y cuentas lo agrupado, sino que haces sumas de los datos según un criterio propio.

Prueba a ver si es eso lo que quieres.

[chelodelsur]

por Jurena

Muchas gracias quedó excelente

Marcelo

Aquí dejo el codigo tal como quedó, por si le sirve a alguien

SELECT Year(log_0.fecha_date) as `año`, MONTHNAME(log_0.fecha_date) as `Mes` ,
SUM(IF(t_mensaje='',1,0)) AS VACIO,
SUM(IF(t_mensaje='NORMAL',1,0)) AS NORMAL,
SUM(IF(t_mensaje='CODIGO',1,0)) AS CODIGO,
SUM(IF(t_mensaje='GENERAL',1,0)) AS GENERAL,
SUM(IF(t_mensaje='GENERAL PRE',1,0)) AS GENERAL_PRE,
SUM(IF(t_mensaje='HOTLINE',1,0)) AS HOTLINE,
SUM(IF(t_mensaje='HOTLINE-ANEXO',1,0)) AS HOTLINE_ANEXO,
SUM(IF(t_mensaje='NORMAL' OR t_mensaje='CODIGO' OR t_mensaje='GENERAL' OR t_mensaje='GENERAL PRE' OR t_mensaje='HOTLINE'
OR t_mensaje='HOTLINE-ANEXO',1,0)) AS CALLCENTER1,
SUM(IF(t_mensaje='' OR t_mensaje='NORMAL' OR t_mensaje='CODIGO' OR t_mensaje='GENERAL' OR t_mensaje='GENERAL PRE' OR t_mensaje='HOTLINE'
OR t_mensaje='HOTLINE-ANEXO',1,0)) AS CALLCENTER2
FROM ivr.log log_0
WHERE log_0.fecha_date>='2008-04-01'
AND log_0.fecha_date<='2008-06-31'
GROUP BY YEAR(log_0.fecha_date),MONTH(log_0.fecha_date)
;

[chelodelsur]

Jurena, soy yo nuevamente

a ver si me dedicas unos minutos más, mira para el mismo código del ultimo post ahora quiero agregarle otra condición a una de las sumas, me explico

Código:

SELECT Year(log_0.fecha_date) as `año`, MONTHNAME(log_0.fecha_date) as `Mes` ,
SUM(IF(t_mensaje='',1,0)) AS VACIO,
SUM(IF(t_mensaje='NORMAL',1,0)) AS NORMAL,
SUM(IF(t_mensaje='CODIGO',1,0)) AS CODIGO, 
SUM(IF(t_mensaje='GENERAL',1,0)) AS GENERAL,
SUM(IF(t_mensaje='GENERAL PRE',1,0)) AS GENERAL_PRE,
SUM(IF(t_mensaje='HOTLINE',1,0)) AS HOTLINE,
SUM(IF(t_mensaje='HOTLINE-ANEXO',1,0)) AS HOTLINE_ANEXO,
SUM(IF(t_mensaje='NORMAL' OR t_mensaje='CODIGO' OR t_mensaje='GENERAL' OR t_mensaje='GENERAL PRE' OR t_mensaje='HOTLINE'
OR t_mensaje='HOTLINE-ANEXO',1,0)) AS CALLCENTER1, 
SUM(IF(t_mensaje='' OR t_mensaje='NORMAL' OR t_mensaje='CODIGO' OR t_mensaje='GENERAL' OR t_mensaje='GENERAL PRE' OR t_mensaje='HOTLINE'
OR t_mensaje='HOTLINE-ANEXO',1,0)) AS CALLCENTER2 
FROM ivr.log log_0
WHERE log_0.fecha_date>='2008-04-01' 
AND log_0.fecha_date<='2008-06-31' 
GROUP BY YEAR(log_0.fecha_date),MONTH(log_0.fecha_date)
;

SUM(IF(t_mensaje='NORMAL' OR t_mensaje='CODIGO' OR t_mensaje='GENERAL' OR t_mensaje='GENERAL PRE' OR t_mensaje='HOTLINE'
OR t_mensaje='HOTLINE-ANEXO',1,0)) AS CALLCENTER1

Necesito que este campo solo sume los valores descritos si en el campo trf_agente (otro campo de la misma tabla) el valor para ese registro es '', o sea vacio.
Una vez más muchas gracias a todos los que me ha dedicado su valioso tiempo y conocimientos

[chelodelsur]

La solución estaba en mis narices y no la veía.

Bueno eso pasa cuando se está viciado en una idea, al final la solución era hacer la consulta al reves, utilizando el IF tal como la anterior, así quedó esta parte del codigo ahora

sum(IF(trf_agente='',t_mensaje='CODIGO' OR t_mensaje='GENERAL' OR t_mensaje='GENERAL PRE' OR t_mensaje='HOTLINE'OR t_mensaje='HOTLINE-ANEXO',0))as CALLCENTER1,

Muchas gracias de todos modos por toda la ayuda

Marcelo

[MLIZAMAG]

Estimados junto con saludarlos y ojala me puedan ayudar, tengo un problema que me esta rompiendo la cabeza, el tema es que tengo un codigo pero este tiene 56 registros para el mismo pero el tema es que estan de manera horinzal y yo necesito dejarlos de manera vertical sin que pierda su encadenacion con el registro unico,

no se si me explique Ej

Asi esta
dato 1 dato2 etc, etc dato56
Nº1 x x x

Necesito que quede

Nº1 dato1
dato2
dato3
dato56
sin que pierda su nombre

Gracias