Problems with the MAX() IN SQL: not returning all desired information

Question

I am exploring a dataset in Microsoft SQL Server Management, regarding sales. I want to obtain the day with the highest number of items sold for each year, therefore a table like this (the values in the rows are totally random):

Year	Purchase Day	Max_Daily_Sales
2011	2011-11-12	48
2012	2012-12-22	123

I first tried to run this query:

WITH CTE_DailySales AS
( 
    SELECT DISTINCT 
        Purchase_Day, 
        Year, 
        SUM(Order_Quantity) OVER (PARTITION BY Purchase_Day, Year) AS Daily_Quantity_Sold
    FROM 
        [sql_cleaning].[dbo].[Sales$]
)
SELECT   
    Year, MAX(Daily_Quantity_Sold) AS Max_Daily_Sales
FROM 
    CTE_DailySales
GROUP BY  
    Year
ORDER BY  
    Year

It partially works since it gives me the highest quantity of items sold in a day for each year. However, I would also like to specify what day of the year it was.

If I try to write Purchase_Day in the Select statement, it returns the max for each day, not the single day with the highest number of items sold.

How could I resolve this problem?

I hope I've been clear enough and thanks you all for your help

Is `Year` not always equal to `DATEPART(Year from Purchase_Day)` ? I think there is no reason for grouping on year. — Luuk, Apr 25 '22 at 13:39
Relevant : https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — Charlieface, Apr 25 '22 at 14:37
@Luuk Yes, you are right. I only feel more confident keeping both Year and Purchase_Day for this query — Gabriele. F, Apr 25 '22 at 15:14

Dordi · Answer 1 · 2022-04-25T15:02:12.323

1

I suggest you use ROW_NUMBER to get you max value, your query would be:

WITH CTE_DailySales AS
( 
    SELECT Purchase_Day, 
        Year, 
        SUM(Order_Quantity) Daily_Quantity_Sold,
        ROW_NUMBER() OVER(PARTITION BY Year ORDER BY SUM(Order_Quantity) DESC) as rn
    FROM 
        [sql_cleaning].[dbo].[Sales$]
    GROUP BY Purchase_Day, 
        Year
)
SELECT   
    *
FROM 
    CTE_DailySales
WHERE rn = 1

edited Apr 25 '22 at 15:02

answered Apr 25 '22 at 12:08

Dordi

778
1
5
14

Thank for your answer, but it doesn't give me what I would like, since it returns all days for each year. Instead, I would like to have, for each year, the one day that had the biggest number of items sold. – Gabriele. F Apr 25 '22 at 15:01
@Gabriele.F i updated the query, you're right, in the partition by i should take only the Year – Dordi Apr 25 '22 at 15:03
It worked perfectly this time, it returns what I was looking for. Thanks for your help! – Gabriele. F Apr 25 '22 at 15:07

score 0 · Answer 2 · answered Apr 25 '22 at 12:18

0

Simply :

SELECT Purchase_Day, 
       Year, 
       SUM(Order_Quantity) OVER(PARTITION BY Purchase_Day, Year) AS Daily_Quantity_Sold,
       MAX(SUM(Order_Quantity)) OVER(PARTITION BY Purchase_Day, Year) AS MAX_QTY_YEAR
FROM   [sql_cleaning].[dbo].[Sales$];

answered Apr 25 '22 at 12:18

SQLpro

3,994
1
6
14

Thank you for your answer. However, It doesn't work since it gives me the following error: If I try < GROUP BY Year, Purchase_Day and Order_Quantity>, it returns all days in the table, the sum of the items sold and the biggest transaction of the day (e.g.: I sold 8 items total in day x, and the biggest transaction was 3 items). – Gabriele. F Apr 25 '22 at 14:45
Sorry I was wrong. Try the last version I wrote – SQLpro Apr 25 '22 at 19:48

Problems with the MAX() IN SQL: not returning all desired information

2 Answers2