SQL Error (1111): Invalid use of group function

Question

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

1

I have a MySQL query to extract the budgets of the last year (the highest year registered in the table):

select *
from Presupuestos
where year(fecha) = max(year(fecha))

the date column is of type DATE.

When executing the query, it shows me:

SQL Error (1111): Invalid use of group function

Any solution? Thanks

mysql query

asked by David 05.02.2018 в 09:07

source

3 answers

score 2 · Answer 1

If you try to use an aggregate function in a context where a non-aggregate function is expected, then you receive the error:

SQL Error (1111): Invalid use of group function

In your particular case, you are using YEAR() in block WHERE , which is a context in which such type of functions is not allowed.

To solve this you could use HAVING , which does allow added functions:

SELECT
     *
FROM
     Presupuestos
HAVING
     year(fecha) = max(year(fecha))

score 1 · Answer 2

1

You can not use functions such as year () sum () or concat () in the conditions part. Test

select max(year(fecha)) from Presupuestos;

answered by 05.02.2018 в 09:30

score 0 · Answer 3

0

I have found a solution

select *
from Presupuestos
where year(fecha) = (select max(year(fecha)) from Presupuestos)

answered by 06.02.2018 в 09:58