List Categories with Average Product Price Greater Than 100
List Categories with Average Product Price Greater Than 100
products table:
Categories Table:
Query Explanation
● USE SalesInventoryDB;
Specifies the database to be used for executing the SQL query.
● SELECT Categories.CategoryName, AVG(Products.UnitPrice) AS AvgPrice
Selects the name of each category and calculates the average unit price of all products in that category.
The result is displayed under the alias AvgPrice.
● FROM Products
Begins the query from the Products table which contains unit prices and category IDs.
● JOIN Categories ON Products.CategoryID = Categories.CategoryID
Performs an INNER JOIN between Products and Categories tables based on matching CategoryID to get category names.
● GROUP BY Categories.CategoryName
Groups the results by category to calculate the average unit price for each one.
● HAVING AVG(Products.UnitPrice) > 100
Filters out only those groups (categories) where the average unit price is greater than 100.
SQL Query:
USE SalesInventoryDB;
SELECT Categories.CategoryName, AVG(Products.UnitPrice) AS AvgPrice
FROM Products
JOIN Categories ON Products.CategoryID = Categories.CategoryID
GROUP BY Categories.CategoryName
HAVING AVG(Products.UnitPrice) > 100;
Output:
