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