SQL Top 10 Sales Every Month

Question

Greeting all. I have a SQL 2008 express database, lets name is tbl_Merchant, similar as following:

Merchant | Sales | Month
Comp.1      100     1
Comp.2      230     1
Comp.3      120     1
Comp.1      200     2
Comp.2      130     2
Comp.3      240     2
Comp.1      250     3
  .          .      .
  .          .      .
  .          .      .

I need to find the top 10 merchant with sales every month over 12 months.

It is very easy if it is just one month.

SELECT TOP 10 
       Merchant, 
       Sales, 
       Month
FROM   tbl_Merchant
WHERE  Month = 1
ORDER BY Sales DESC

But I am stuck if I wan to find them over 12 months. I need to display 120 merchants, which are top 10 sales merchant of each month. Currently my solution is to union 12 tables from month 1 to 12 together but I don't think it is a good way.

Can any one kindly give me any suggestion?

Thank you very much.

Which SQL (MySQL, Oracle, PostgreSQL, SQLServer, etc) are you using? — , Jan 10 '12 at 09:44
@MarkBannister: `TOP 10` indicates SQL Server or Sybase (both `T-SQL`) — Lukas Eder, Jan 10 '12 at 10:02

Oleg Dok · Accepted Answer · 2012-01-10T10:00:26.673

8

Valid for Sql Server 2005+

Try this for overall:

SELECT TOP 10 
       Merchant, 
       SUM(Sales) Sales
FROM   tbl_Merchant
WHERE  Month BETWEEN 1 and 12
GROUP BY Merchant
ORDER BY 2 DESC

OR

if you need details per month for top 10 PER MONTH

;WITH MonthsCTE(m) as
(
    SELECT 1 m
    UNION ALL 
    SELECT m+1
    FROM MonthsCTE
    WHERE m < 12
)
SELECT m [Month], t.*
FROM MonthsCTE
CROSS APPLY 
(
    SELECT TOP 10
       Merchant, 
       SUM(Sales) Sales
    FROM   tbl_Merchant
    WHERE  Month = MonthsCTE.m
    GROUP BY Merchant
    ORDER BY 2 DESC
) t

edited Jan 10 '12 at 10:00

answered Jan 10 '12 at 09:43

Oleg Dok

21,109
4
45
54

this is not correct: SUM(Sales) Sales. Read the question. Sales are already sum for each merchant/month. – giammin Jan 10 '12 at 10:06

score 5 · Answer 2 · edited Jan 10 '12 at 10:14

5

Try:

select * from
(SELECT Merchant, 
        Sales, 
        Month,
        row_number () over (partition by Month order by Sales desc) rn
 FROM   tbl_Merchant
 WHERE  Month between 1 and 12) v
where rn <= 10
order by Month, Sales desc

(Should work in SQLServer; not sure about Sybase.)

edited Jan 10 '12 at 10:14

Lukas Eder

211,314
129
689
1,509

answered Jan 10 '12 at 10:09

score 0 · Answer 3 · answered Jul 10 '19 at 05:51

select Top 10 
         SaleID, 
         sum(Salelines) As LINESUM
Into #TOP10sales
from salestable
group by SaleID
order by sum(Salelines) asc;

Then confirm the sales are top 10

select * 
from #TOP10sales;

Then.

Select field..
       field..
       field..
from #TOP10sales 
Inner Join salestable on #TOP10sales.ID = salestable.ID;

sandysmith · Answer 4 · 2012-01-10T10:04:32.790

Can try the following:

SELECT top 10 Merchant,
isnull(SUM(CASE WHEN Month(ta.dt) = '01' THEN Sales END),0) AS "Jan Sales",
isnull(SUM(CASE WHEN Month(ta.dt) = '02' THEN Sales END),0) AS "Feb Sales",
isnull(SUM(CASE WHEN Month(ta.dt) = '03' THEN Sales END),0) AS "Mar Sales",
isnull(SUM(CASE WHEN Month(ta.dt) = '04' THEN Sales END),0) AS "Apr Sales",
isnull(SUM(CASE WHEN Month(ta.dt) = '05' THEN Sales END),0) AS "May Sales",
isnull(SUM(CASE WHEN Month(ta.dt) = '06' THEN Sales END),0) AS "Jun Sales",
isnull(SUM(CASE WHEN Month(ta.dt) = '07' THEN Sales END),0) AS "Jul Sales",
isnull(SUM(CASE WHEN Month(ta.dt) = '08' THEN Sales END),0) AS "Aug Sales",
isnull(SUM(CASE WHEN Month(ta.dt) = '09' THEN Sales END),0) AS "Sep Sales",
isnull(SUM(CASE WHEN Month(ta.dt) = '10' THEN Sales END),0) AS "Oct Sales",
isnull(SUM(CASE WHEN Month(ta.dt) = '11' THEN Sales END),0) AS "Nov Sales",
isnull(SUM(CASE WHEN Month(ta.dt) = '12' THEN Sales END),0) AS "Dec Sales"

FROM tbl_Merchant ORDER BY Sales DESC

Top 10 sales of every month will be displayed as different columns by month.

Hope it helps u.

score -1 · Answer 5 · answered Jan 10 '12 at 09:40

-1

select top 10 Merchant, sum(Sales) from tbl_Merchant group by Merchant order by sum(Sales) desc

I'm more postgresql guy, but i think it could work without (or with little) modification in mssql

answered Jan 10 '12 at 09:40

Adrian Serafin

7,665
5
46
67

hmm... why -1 for this answer? – Adrian Serafin Jan 10 '12 at 09:54

Vivek · Answer 6 · 2012-01-11T09:59:50.720

-1

Details per month for top 10 PER MONTH can be derived using this query too

select merchant,SUM(sales)Total_sales,month as Monthofyear
from tbl_Merchant
group by month,merchant,sales
order by month,sales desc

edited Jan 11 '12 at 09:59

answered Jan 10 '12 at 10:16

Vivek

318
1
5
17

Sorry, it only lists out the top 10. It is not I want. – DragonZelda Jan 11 '12 at 03:17

SQL Top 10 Sales Every Month

6 Answers6

Linked