CODEWITHSUNDEEP
sqlstatistics
DragonZelda
DragonZelda

Reputation: 168

SQL Top 10 Sales Every Month

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.

Upvotes: 4

Views: 22692

Answers (6)

Dennis Akwensioge
Dennis Akwensioge

Reputation: 11

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;

Upvotes: 0

Vivek
Vivek

Reputation: 329

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

Upvotes: -1

user359040
user359040

Reputation:

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

Upvotes: 5

sandysmith
sandysmith

Reputation: 51

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.

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21776

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

Upvotes: 8

Adrian Serafin
Adrian Serafin

Reputation: 7725

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

Upvotes: -1

Related Questions