2

I'm trying to discover the foreign keys which appear more than once for all rows in table and return those foreign keys via a subquery.

Using GROUP BY on the column I was able to visualize the COUNT of each foreign key:

SELECT orders_id, COUNT(*) as count
FROM order_history
GROUP BY orders_id

but then when I augment it with a WHERE clause in an attempt to filter, like this:

SELECT orders_id, COUNT(*) as count
FROM order_history
GROUP BY orders_id
WHERE count > 1

I get an error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE count > 1 LIMIT 0, 30' at line 4

apparently count is ineligible for comparisons at the point WHERE is evaluated?

: Post answer update :

Thanks for the speedy responses! HAVING is exactly what I was missing. I found this question helpful expansion of the topic: WHERE vs HAVING

Community
  • 1
  • 1
Mark Fox
  • 8,694
  • 9
  • 53
  • 75
  • 1
    The error is common - *the order of components matters in an SQL select statement*. As in this case, `WHERE`, if present, must always come before `GROUP BY`; the fundamental issue here is *invalid syntax* and not an unbound identifier. (Selects can be nested, but there are better ways to handle this.) –  Dec 28 '12 at 21:04

2 Answers2

3

To apply filtering to an aggregate value like COUNT(),SUM(),MAX(),MIN(), use a HAVING clause rather than WHERE.

SELECT orders_id, COUNT(*) as count
FROM order_history
GROUP BY orders_id
HAVING count > 1

The syntax error results from the fact that a WHERE clause must precede the GROUP BY clause. However, even if it was in the right place, it would still error because the count alias would be unknown at the time the WHERE clause was computed.

From the MySQL SELECT syntax documentation:

A HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
1

You can not use where clause with group by clause, in this case you have to use having 

SELECT orders_id, COUNT(*) as count
FROM order_history
GROUP BY orders_id
having count > 1
Raghvendra Parashar
  • 3,883
  • 1
  • 23
  • 36