-1

I have query with a HAVING clause that's not giving the expected result.

The query is below: 

SELECT COUNT(ClientProductID), SubCategoryName 
FROM v_EnterpriseStructure #
GROUP BY ClientProductID, SubCategoryName Having (PackSizeNum) > 5

And the error I get is below (the HAVING clause is included in the GROUP BY clause, so I don't understand why it would give this error?). Can anyone shed any light, please?

Msg 8121, Level 16, State 1, Line 121 Column 'v_EnterpriseStructure.PackSizeNum' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Mr 2017
  • 103
  • 2
  • 6
  • 15
  • That answers part of it, but not all of it. I've added PackSizeNum to the GROUP BY clause, which gets rid of the error (thanks). However, the results I get look like this (below). Every result is '1' but it should return the number of products with a Pack Size that's greater than 5 in each SubCategory. Any other ideas? Thanks in advance. (No column name) SubCategoryName 1 DOGTREAT S. 1 STYLIN G. 1 CANNEDSOU P. – Mr 2017 Jan 07 '20 at 16:48
  • 1
    @Mr2017 the duplicate *is* the full answer. You *can't* use arbitrary columns in the `HAVING` clause. They have to be part of the GROUP BY clause. As for the results, are you sure `1` is the *wrong* value? How many `ClientProductID`s are there per `SubCategoryName` ? You'll have to provide sample data and the expected output for people to help – Panagiotis Kanavos Jan 07 '20 at 17:07

1 Answers1

1

Your GROUP BY should not have a ClientProductID as you being called in aggregation COUNT() :

SELECT COUNT(ClientProductID), SubCategoryName 
FROM v_EnterpriseStructure #
GROUP BY SubCategoryName 
HAVING COUNT(PackSizeNum) > 5;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52