I am struggling to write a mysql query to help me find the information I am looking for. I have a table that records sales of different items.
I have a table(tblsales) with four columns: Date, Item, PricePer, Quantity.
Some days I have 2 sales and some days I have 200 sales.
I would like to write a query that shows me the average item price of the five largest quantity sales I made for every day I have recorded a sale.
The query would output a table with two columns: Date, Average_Price_Top_Five_Sellers
I can't figure out how to write the query. I can write it for a single day, but I can't figure out how to properly add the limit clause to query for the full calendar.
SELECT Date, AVG(Price) AS Average_Price_Top_Five_Sellers
FROM tblsales
WHERE Date = "2023-01-01"
ORDER BY Quantity DESC
LIMIT 5;
Can you please help me to write a query that returns the information I am looking for? Thanks.
If I understand your question correctly, you can use window functions:
select date, avg(price) avg_price
from (
select s.*,
rank() over(partition by date order by quantity desc) rn
from tblsales s
) s
where rn <= 5
group by date
This produces one row per date when you had sales, with the average price of the top 5 sales (by quantity) on that day.