4

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
Gerrit Botes
  • 57
  • 1
  • 2
  • 6
  • 1
    MySQL and SQL Server are two different things; they're not synonymous. Use only the tag that is relevant to your question, and don't just randomly add others that contain familiar words or sound similar. If you're not sure whether a tag applies, read the tag description. If you're still not sure, don't use it; if it needs to be added, someone here will add it for you. Tags here have relevance and specific meanings. Thanks. – Ken White Apr 19 '16 at 22:16
  • Oh, thank you, edited. I'm new here so I wasn't entirely sure, thanks for the heads up. – Gerrit Botes Apr 20 '16 at 07:19

1 Answers1

10

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.

Kateract
  • 822
  • 6
  • 15
  • Thank you for the help, I reconsidered my code after reading what you said about the `COUNT` and `DISTINCT` , I can't use an orderid since the table lists multiple customerid's for each order, eg. if customerid 2234 made 3 orders then column customerid would have 3 2234 values. It then has columns for what they ordered and on what date and the price of the order. What I ended up doing is just using a `COUNT` for customerid and putting it alongside customerid column. Which gives me the desired result of showing how many customerid's exist for each customerid. – Gerrit Botes Apr 20 '16 at 07:37
  • This ended up working for me `USE MyCompany;` `GO` `SELECT COUNT(customerid) AS NumberOfOrdersMade, customerid AS CustomerID, SUM(price) AS SUMOfPriceOfordersMade` `FROM tblItems_Ordered` `GROUP BY customerid` `GO` – Gerrit Botes Apr 20 '16 at 07:39
  • Forgot the `HAVING` `'HAVING COUNT(customerid) > 1` – Gerrit Botes Apr 20 '16 at 07:45
  • `HAVING COUNT(*) > 1` should suffice. – Disillusioned Jan 22 '17 at 12:45