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):

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

CodePudding user response：

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

CodePudding user response：

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$];