Welcome to Excel Jet Consult blog post. In this tutorial, we are going to consider how to write sql query to return the products with a price above the yearly average price. Let’s dive in.
With a simple SELECT * FROM factSalesTable, we fetched all the records in the table as seen below
To find all products with a price above the yearly average price, we are going to write the code and execute the code below:
SELECT Products, AVG(Price) AS AvgPrice FROM factSalesTable GROUP BY Products HAVING AVG(Price) > (SELECT AVG(Price) FROM factSalesTable);
In the SELECT statement, we specify that we want to display two columns: “Products” and the calculated average price (aliased as “AvgPrice”).
Next, we specify the source table as “factSalesTable,” which contains sales data, including product names and prices.
Thereafter, we group the results by the “Products” column. The HAVING clause filters the grouped results based on specified condition. We calculate the average price for each product within the groups using the AVG(Price) function.
The subquery (SELECT AVG(Price) FROM factSalesTable) calculates the overall average price across all products in the “factSalesTable.” The condition AVG(Price) > (SELECT AVG(Price) FROM factSalesTable) ensures that only products with an average price higher than the overall average are included in the result set as seen in the caption below:
The above query which looks fairly complex is handy for price optimization, determining promotional pricing and benchmarking.