SQL: Distinct count in where clause?

Question

My order table looks like this:

id   fk_customer
1    34
2    34
3    34
4    7
5    7
6    8

I would like to select only those customers who have more than 2 orders.

This doesnt work however:

SELECT * FROM order WHERE COUNT DISTINCT fk_customer > 2

Please advice!

thanks

Answer 1

Try this:

SELECT fk_customer, COUNT(*)   
FROM dbo.[Order]
GROUP BY fk_customer
HAVING COUNT(*) > 2

Order is a reserved word in most SQL based database systems - bad choice for a table name.... in SQL Server, you need to put it in square brackets to make it clear it's an object name (not the reserved word) to be used.