CODEWITHSUNDEEP
sql-server
user8944284
user8944284

Reputation:

How to get the second to the highest monthly sales for every year

I'm trying to get the second to the highest monthly sales for every year.

So far I'm getting the second highest monthly sales for the first year only.

WITH newtable AS
(
    SELECT 
    MONTH(o.orderdate) AS 'MONTH',
    YEAR(o.orderdate) AS 'YEAR',
    SUM(od.qty*od.unitprice) AS monthSales
    FROM Sales.Orders AS o
    INNER JOIN Sales.OrderDetails AS od
    ON o.orderid = od.orderid
    GROUP BY YEAR(o.orderdate), MONTH(o.orderdate) 
)
SELECT YEAR, MAX(monthSales) AS secondHighestMonthlySales
FROM newtable
WHERE monthSales < (SELECT MAX(monthSales) FROM newtable)
GROUP BY YEAR;

I need the second highest for every year.

Upvotes: 0

Views: 547

Answers (2)

SQL_M
SQL_M

Reputation: 2475

As an alternative, you can use the ROWNUMBER() function. In this case, I use a common table expression. We find the second highest number of total sales for each year. These years are the so-called partitions.

USE TEMPDB

CREATE TABLE #Sales (SalesYear INT, TotalAmount INT)
INSERT INTO #Sales VALUES (2016, 1100), (2016, 700), (2016, 950),
                          (2017, 660), (2017, 760), (2017, 460),
                          (2017, 141), (2018, 999), (2018, 499);

WITH CTE AS 

(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY SalesYear ORDER BY TotalAmount DESC) AS RowNumb 
    FROM #Sales
)

SELECT SalesYear,
      TotalAmount
FROM CTE WHERE RowNumb = 2

Upvotes: 0

Chiranjib
Chiranjib

Reputation: 1783

Assuming you have the data correct in newtable, focus on the second query regarding what you want. This is pure SQL:

Test Data:

Year    Sales
2010    500
2010    400
2010    600
2011    700
2011    800
2011    900
2012    400
2012    600
2012    500

Query to select the second highest:

select O.year, max(O.sales) as secondhighestsale from Orders O,
(select year, max(sales) as maxsale
from Orders
group by year) A
where O. year = A.year
and O.sales < A.maxsale
group by O.year

Output:

year    secondhighestsale
2010    500
2011    800
2012    500

Upvotes: 1

Related Questions