misuse of aggregate function

This error occurs when you use an aggregate function (SUM, COUNT, AVG, etc.) incorrectly, such as in a WHERE clause or nested in another aggregate.

The misuse of aggregate function error means you're using an aggregate function in a place where it's not allowed.

Understanding the Error

Error: misuse of aggregate function SUM()

Aggregate functions like SUM, COUNT, AVG, MIN, MAX have rules about where they can appear.

Common Causes

1. Aggregate in WHERE Clause

SQL

-- Wrong: can't use SUM in WHERE
SELECT * FROM orders
WHERE SUM(amount) > 1000;

-- Correct: use HAVING
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

2. Nested Aggregates

SQL

-- Wrong: SUM inside AVG
SELECT AVG(SUM(amount))
FROM orders
GROUP BY customer_id;

-- Correct: use subquery
SELECT AVG(total)
FROM (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
);

3. Aggregate Without GROUP BY

Mixing aggregate and non-aggregate columns:

SQL

-- Wrong in standard SQL (SQLite allows but picks arbitrary row)
SELECT name, SUM(amount) FROM orders;

-- Correct: either all aggregate or use GROUP BY
SELECT SUM(amount) FROM orders;
-- or
SELECT name, SUM(amount) FROM orders GROUP BY name;

4. Aggregate in JOIN Condition

SQL

-- Wrong
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id AND COUNT(o.id) > 5;

-- Correct: filter after aggregation
SELECT u.*, order_count
FROM users u
JOIN (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
  HAVING COUNT(*) > 5
) o ON u.id = o.user_id;

How to Fix It

Solution 1: Use HAVING Instead of WHERE

SQL

-- Filter on aggregate results
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100;  -- Not WHERE

Solution 2: Use Subquery

SQL

-- Calculate aggregate in subquery
SELECT *
FROM customers c
WHERE (
  SELECT SUM(amount) FROM orders WHERE customer_id = c.id
) > 1000;

Solution 3: Use CTE (Common Table Expression)

SQL

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT c.*, ct.total
FROM customers c
JOIN customer_totals ct ON c.id = ct.customer_id
WHERE ct.total > 1000;

Solution 4: Window Functions (SQLite 3.25+)

SQL

SELECT
  id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders
WHERE amount > 100;  -- Filter on non-aggregate column

WHERE vs HAVING

WHERE: Filters rows BEFORE aggregation
HAVING: Filters groups AFTER aggregation

SQL

SELECT category, COUNT(*) AS product_count
FROM products
WHERE price > 10           -- Filter rows first
GROUP BY category
HAVING COUNT(*) >= 5;      -- Then filter groups

Best Practices

Use WHERE for row-level filters
Use HAVING for aggregate filters
Use subqueries for complex aggregate logic
Consider CTEs for readability
Test with sample data to verify results