AI Hints
Homeโ€บSQL Tutorialโ€บReading Error Messages
SQL ยท Lesson 106 of 126

Ambiguous and Misused

Two thinking errors.

โฑ 2 min readโ–ถ Video at 2:30:03๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

These two errors aren't typos. They mean SQL doesn't know what you mean, or you've asked it to do something in the wrong place.

Ambiguous and Misused
From the video: Ambiguous and Misused
  • Not typos
  • SQL doesn't know what you mean
  • Or the wrong place

Ambiguous column

Both orders and customers have a customer ID column. So which one do we mean? Run it. Ambiguous column name. SQL refuses to guess.

query.sqlsql
SELECT customer_id, order_date
FROM orders
JOIN customers
  ON customers.customer_id = orders.customer_id;
Output
Parse error near line 1: ambiguous column name: customer_id
  SELECT customer_id, order_date FROM orders JOIN customers   ON customers.custo
         ^--- error here

The fix: say which table

Put the table name, or alias, in front of the column. Run it. Now SQL knows exactly which customer ID we mean.

query.sqlsql
SELECT orders.customer_id, order_date
FROM orders
JOIN customers
  ON customers.customer_id = orders.customer_id
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ customer_id โ”‚ order_date โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1           โ”‚ 2025-03-06 โ”‚
โ”‚ 1           โ”‚ 2024-11-24 โ”‚
โ”‚ 2           โ”‚ 2024-06-22 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Misuse of aggregate

Here we try to filter on count, inside where. Run it. Misuse of aggregate. Remember, where runs before grouping, so counts don't exist yet.

query.sqlsql
SELECT genre, COUNT(*)
FROM books
WHERE COUNT(*) > 3
GROUP BY genre;
Output
Parse error near line 1: misuse of aggregate: COUNT()
  SELECT genre, COUNT(*) FROM books WHERE COUNT(*) > 3 GROUP BY genre;
                            error here ---^

The fix: HAVING

Move the test to having, after group by. Run it. Mystery, romance, and thriller each have four books.

query.sqlsql
SELECT genre, COUNT(*) AS books
FROM books
GROUP BY genre
HAVING COUNT(*) > 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  genre   โ”‚ books โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Mystery  โ”‚ 4     โ”‚
โ”‚ Romance  โ”‚ 4     โ”‚
โ”‚ Thriller โ”‚ 4     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Ambiguous means, say which table. Misuse of aggregate means, use having instead of where. Next, the most dangerous mistakes of all. The silent ones.

Key takeaways
  • ambiguous: add the table
  • aggregate in WHERE: use HAVING
  • Errors are helpful
Next up: Silent Mistakes โ€” no error, wrong answer