SQL: invalid use of group function

Asked Dec 20 '17 at 09:52

Active Dec 20 '17 at 09:55

Viewed 37 times

Whilst writing a SQL query for an assignment I ran into this error:

Error Code 1111: Invalid use of group function

and I can't seem to figure out why I get this error. What is wrong with my code?

SELECT sum.id, sum.count
FROM(
    SELECT habit.id AS id, COUNT(*) AS count
        FROM habit
            JOIN habit_done ON habit.id=habit_done.habit_id
            JOIN habit_list ON habit.habit_list_id=habit_list.id
        WHERE habit_list.id=4
        GROUP BY habit_done.habit_id) AS sum
WHERE sum.count>avg(sum.count)

avg(sum.count) gave the average of the number of completions of all habits, I tested this in a previous exercise.

I want to list all the habits that are completed more often than the average number of completions for all habits in the same list

edited Dec 20 '17 at 09:55

René Vogt

43,056
14
77
99

asked Dec 20 '17 at 09:52

Wolf Bubberman

Change to `HAVING sum.count>avg(sum.count)` – M Khalid Junaid Dec 20 '17 at 09:55
I currently can't test the correct syntax, but I guess your `WHERE` clause should be a `HAVING` clause... – René Vogt Dec 20 '17 at 09:56
that fixed the error, thank you! <3 – Wolf Bubberman Dec 20 '17 at 09:57
it's only selecting 1 row now though, any idea why? – Wolf Bubberman Dec 20 '17 at 10:01

SQL: invalid use of group function

0 Answers0