Use the COUNT () function and a WHERE in the same Query

Question

Use the COUNT () function and a WHERE in the same Query

Navigation

#1 by (5 votes)

1

I have the following query:

SELECT 
COUNT(IDPROVEEDORES),
IDPROVEEDOR 
FROM PROVEEDORES
WHERE COUNT(IDPROVEEDORES) > 1

But I get the following error:

Incorrect syntax near the keyword 'where'.

sql sql-server

asked by ARR 22.05.2018 в 23:53

source

1 answer

Execution time permissions How do I ignore a directory for pull?

score 5 · Accepted Answer

When the filtering condition you want to do on the result of the aggregation function, then you must use the statement HAVING , not a WHERE (this is because the WHERE is used to filter the data with which the aggregation function is calculated).

In your case, you should use:

SELECT COUNT(IDPROVEEDORES), IDPROVEEDOR
FROM PROVEEDORES
GROUP BY IDPROVEEDOR
HAVING COUNT(IDPROVEEDORES) > 1
;