Aggregate Functions... Group By...

[miffe]

Hey guys,I'd appreciate a bit of help with a problem I am facing... With the dreaded GROUP BY clause.I have the following table, with some data, just so you can get a grip of the situation:

SELECT UNIQUE p.proyecto AS proyecto, DATE_FORMAT(r.inicio,"%d/%m/%Y") AS fecha, DATE_FORMAT(r.tiempo,"%H:%i:%S") AS tiempo, a.problema AS problemaFROM registros AS rINNER JOIN actividades AS a ON r.idActividad = a.idActividadINNER JOIN proyectos AS p ON a.idProyecto = p.idProyectoGROUP BY problema, fecha

Unfortunately, that's not the solution... any help?Thanks,miffeP.S. I'm not sure if I explained myself correctly, if necessary I'll rephrase my question, I kind of extend myself too much sometimes, sorry about that.

[jesh]

If, in your DBMS, DATE_FORMAT(r.inicio,"%d/%m/%Y") converts the inicio field to just the date, then have you tried:

GROUP BY problema, DATE_FORMAT(r.inicio,"%d/%m/%Y")

instead of:

GROUP BY problema, fecha

[miffe]

I tried it just now... heres the code

select problema , proyecto , DATE_FORMAT(r.inicio,"%d/%m/%Y") AS fecha , SUM(DATE_FORMAT(r.tiempo,"%H:%i:%S")) AS tiempo from registros as r inner join actividades as a on r.idactividad = a.idactividad inner join proyectos as p on a.idproyecto = p.idproyecto group by `fecha` ,`problema` ,`tiempo` ,`DATE_FORMAT(r.inicio,"%d/%m/%Y")` ,`proyecto`

And the error is:Error in query: Error# 1054: Unknown column 'DATE_FORMAT(r.inicio,"%d/%m/%Y")' in 'group statement'

[justsomeguy]

Don't put backquotes around the function, backquotes are for table and column names. That's not a column name. But since you gave it an alias in the select list you should be able to use the alias like you were.I don't think you're using group by correctly though. You use group by with other functions like AVERAGE, COUNT, SUM, etc. If you have a statement where you select a SUM and then do a group by on another column, then each row in the result will be the value of the second column and the sum of whatever was being added up for the rows that have the same value in the second column. You don't have any aggregate functions in your select list so it doesn't make sense to use group by. You probably want to use order by instead. If you group by problema, then that means there will be 1 row in the result for each value of problema. So there will be 1 row where problema equals 115, and whatever aggregate information you want about all the rows that have 115 for problema. It doesn't make sense to select dates and things like that and then use group by. You could select the maximum date, or the minimum date, but it doesn't make sense to select all the dates for 1 row in the result.

[miffe]

Thanks a lot for your help ^^I managed to fix it re-saving the 'time' as (date) + 00:00:00 so that way dates would match up by date, all having the same time. And yes, my group by clause was wrong, and I wasn't using the aggregate function properly, managed to fix it now :)Thanks a lot,miffe.