Brainlag
  1. Library
  2. Debugging
  3. SQL

misuse of aggregateWhat is "misuse of aggregate"?

SQLite stops with "misuse of aggregate" when COUNT, SUM or AVG is used where single rows are being tested, usually in WHERE. Group totals are filtered with HAVING.

SQL5 min

What SQLite is telling you

Output
misuse of aggregate: COUNT()

An aggregate like COUNT(), SUM() or AVG() needs a whole group of rows to work on. WHERE looks at one row at a time, before any groups exist. So WHERE COUNT(*) > 3 asks a single row how many rows its group has, and the group has not been made yet. SQLite refuses.

Two common shapes:

  • Filtering a group total in WHERE. "Artists with more than 3 songs" belongs in HAVING, which runs after GROUP BY.
  • Comparing each row with an average. WHERE seconds > AVG(seconds) fails too (the message says misuse of aggregate function AVG()). Work the average out in a subquery: WHERE seconds > (SELECT AVG(seconds) FROM songs).

How to find it

The message names the function. Find it in the query: if it sits inside WHERE (or inside another aggregate, like SUM(COUNT(*))), that is the problem. Ask what the test is about. A group's total goes in HAVING after GROUP BY. A comparison of each row with an overall number gets that number from a subquery.

Run it: counting in the wrong clause

Press Run and read the error from the bottom up.

Tables artists, songs, listeners, plays
artists 8 rows, 4 columns
idnamecountrygenre
1Nova LaneUKpop
2The TidewaterUSindie
3Kaito MoriJPelectronic
4Amara OseiGHafrobeats
5Lumen ChoirSEpop
6Rust & VelvetUKrock
7Pixel HeartsKRpop
8Juno BayAUindie
songs 30 rows, 5 columns
idtitleartist_idsecondsyear
1Neon Rain61782022
2Slow Motion11582025
3Paper Planes22332023
4Static12692020
5Golden Hour11622022
6Night Bus71572020
7Echoes22812022
8Sugar Rush12842019
9Low Tide42892019
10Glass House71522020
11Satellite12822025
12Wildfire32142022

First 12 of 30 rows. Query the table to see the rest.

listeners 10 rows, 4 columns
idusernameagecountry
1maya_k15UK
2devraj17IN
3lucia.p16ES
4tom_h14UK
5ana.s18BR
6kenji16JP
7zoe_r15US
8femi17NG
9liv13SE
10oscar.b16NULL
plays 70 rows, 5 columns
idlistener_idsong_iddayskipped
110162026-09-010
2832026-09-010
35162026-09-010
4222026-09-010
55212026-09-010
65232026-09-020
7612026-09-020
8662026-09-020
9822026-09-021
10552026-09-020
117132026-09-020
12262026-09-030

First 12 of 70 rows. Query the table to see the rest.

query.sql to run
SELECT artist_id, COUNT(*) AS songs
FROM songs
WHERE COUNT(*) > 3
GROUP BY artist_id;

Listeners older than average

This should list the username and age of every listener older than the average listener. It stops with "misuse of aggregate function AVG()". Fix it.

exercise_1.sql to run
SELECT username, age
FROM listeners
WHERE age > AVG(age);