Danrex Danrex - 5 months ago 16
SQL Question

How to use column selection in Having Clause but not in Group By Clause in Sql Server

I keep bumping into this problem and I know there must be a simple solution but I can't find it.

I am wanting to get a count of the serials per part between certain dates. So without the date selection it looks like this.

SELECT
Part_Name,
COUNT(DISTINCT Serial_No) AS 'Serial Frequency'
FROM Inventory
GROUP BY Part_Name


And I get a count of serials per part.

part1 55

part2 32

part3 48
etc etc

So now I want this to be selected between two dates. I know I need to use Having but this forces me to add it to the group by section, and then it no longer gives me the count totals but each part individually. I.E.

SELECT
Part_Name,
COUNT(DISTINCT Serial_No) AS 'Serial Frequency'
FROM Inventory
GROUP BY Part_Name, Grade_Date
HAVING Grade_Date > '2016-06-01' AND Grade_Date < '2016-07-01'


part1 1

part1 1

part1 1

part2 1

part2 1
etc etc

What obvious thing am I missing here because surly this can be done? If I don't include it I get this error -
Column 'Inventory.Grade_Date' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

vkp vkp
Answer

Use a where clause because you are comparing actual values not the aggregated ones.

SELECT 
Part_Name,
Grade_Date,
COUNT(DISTINCT Serial_No) AS 'Serial Frequency'
FROM Inventory
WHERE Grade_Date > '2016-06-01' AND Grade_Date < '2016-07-01'
GROUP BY Part_Name,Grade_Date