CODEWITHSUNDEEP
sqlsql-server
ln206
ln206

Reputation: 169

Comparing AVG() in SQL

This is my first SQL query practice and I came up with this problem where I have to find an average of Quantity of items in stock from somewhere house that is greater than the average of a specific warehouse. All i got is a list of the warehouse with their average (the specific warehouse is not included) and I have to keep only the warehouse with its average that is greater than the average of that specific warehouse. How do I solve this? This is my first time learning SQL. Thank you

SELECT Warehouse, AVG(QuantityInStocks) AS Average
FROM SomeTable
WHERE Warehouse Not In ('Specific_Warehouse')
GROUP BY Warehouse;

Upvotes: 2

Views: 8440

Answers (2)

Ivan Gerasimenko
Ivan Gerasimenko

Reputation: 2428

You should use subquery in HAVING clause:

SELECT Warehouse, AVG(QuantityInStocks) AS Average
FROM SomeTable
WHERE Warehouse NOT IN ('Specific_Warehouse')
GROUP BY Warehouse
HAVING AVG(QuantityInStocks) > 
    (SELECT AVG(QuantityInStocks) 
    FROM SomeTable 
    WHERE Warehouse IN ('Specific_Warehouse') 
    GROUP BY Warehouse);

Upvotes: 3

void
void

Reputation: 7880

with regard to what you mentioned in question

"I have to find an average of Quantity of items in stock from somewhere house that is greater than the average of a specific warehouse"

you can use A NESTED SELECT in WHERE CLAUSE:

SELECT AVG(QuantityInStocks) AS Average
FROM SomeTable
WHERE QuantityInStocks > (SELECT AVG(QuantityInStocks)
                          FROM SomeTable)

Upvotes: 1

Related Questions