CODEWITHSUNDEEP
sql-serverhaving
Gerrit Botes
Gerrit Botes

Reputation: 57

SQL Server how can I use COUNT DISTINCT(*) in HAVING clause?

I have a procedure that counts all the unique [customerid] values and displays them in a SELECT list. I'm trying to sort the [customerid] where it is only "> 1" by using a HAVING clause, but SQL won't let me use the DISTINCT COUNT inside the HAVING. In my mind it makes sense that the HAVING should work with the COUNT but it does not: 

USE MyCompany;
GO
SELECT DISTINCT COUNT(customerid) AS NumberOfOrdersMade, customerid AS 
CustomerID
FROM tblItems_Ordered
GROUP BY customerid
HAVING DISTINCT COUNT(customerid) > 1
GO

Upvotes: 4

Views: 48297

Answers (1)

Kateract
Kateract

Reputation: 852

You probably want SELECT COUNT(DISTINCT orderid) instead of DISTINCT COUNT(customerid):

USE MyCompany;
GO
SELECT COUNT(DISTINCT orderid) AS NumberOfOrdersMade, customerid AS
CustomerID
FROM tblItems_Ordered
GROUP BY customerid
HAVING COUNT(DISTINCT orderid) > 1
GO

When outside of the COUNT, the DISTINCT will eliminate duplicate rows from a result set, which will have no effect in your query because you are doing a GROUP BY. When inside the COUNT, DISTINCT will limit the count to unique values of the column that you pass to the count function. Thus, it makes more sense to use an orderid column instead of customerid when you're aliasing it as NumberOfOrdersMade.

Upvotes: 13

Related Questions