CODEWITHSUNDEEP
sqlsql-server
Nitamashu
Nitamashu

Reputation: 1

Finding the number of instances of a variances using SQL Server

I am building a stock synch using SQL. I am trying to query a table that holds the results from comparing product quantities data across multiple tables. I am trying to display the data for the products which have had a misalignment of greater than 0 for 7 days or more This is the query I wrote to try to get this result


 select  Sap_code, Product_description, Location, Total_difference
 from  Misalignment
 where Total_difference > 0  and
 count(Total_difference) > 7
 group by Sap_code, Product_description, Location, Total_difference

I am getting the following error message when I run this query:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

I have included an image of the table I am querying as a point of reference

enter image description here

Upvotes: 0

Views: 31

Answers (1)

Coskun Ozogul
Coskun Ozogul

Reputation: 2487

Does this query give what you want?

SELECT COUNT(Total_difference) AS DiffCount, Sap_code, Product_description
    , Location, Total_difference
FROM Misalignment      
WHERE Total_difference > 0 
GROUP BY Sap_code, Product_description, Location
HAVING COUNT(Total_difference) > 7

Upvotes: 1

Related Questions