CODEWITHSUNDEEP
sql-server-2008
Elie-M
Elie-M

Reputation: 79

Select Top 10 Sales Amounts Every Month, Every Year SQL Server 2008

SELECT tr.createdon, 
       st.be_storecountry, 
       st.be_storelocationcategory,
       st.be_name as storename, 
       br.be_name as brand, 
       SUM(sd.be_amount) as total
from 
       Filteredbe_transaction tr, 
       Filteredbe_store st,
       Filteredbe_salesdetails sd, 
       Filteredbe_brand br
Where
       sd.be_itembrand = br.be_brandid and
       tr.be_storename = st.be_storeid and
       tr.be_transactionid = sd.be_transactionid and
       tr.createdon between '1/1/2008' and '1/1/2014'
group by 
       tr.createdon, st.be_storecountry, st.be_storelocationcategory,
       st.be_name, br.be_name
Order by 
       tr.createdon desc

This is my query and it returns 286 rows. I need to modify it to get the top 10 sales amount from every month, every year. So each month of the year from 2008 to 2014 must have only 10 corresponding results and those must be the max sales amounts. Any one can help please?

Upvotes: 0

Views: 2663

Answers (1)

shree.pat18
shree.pat18

Reputation: 21757

You could use partition to break this into subsets based on month and year, and then select the top 10 from each such subset as your result, like so:

;with cte as
(
 SELECT tr.createdon, 
   st.be_storecountry, 
   st.be_storelocationcategory,
   st.be_name as storename, 
   br.be_name as brand, 
   SUM(sd.be_amount) as total
from 
   Filteredbe_transaction tr, 
   Filteredbe_store st,
   Filteredbe_salesdetails sd, 
   Filteredbe_brand br
Where
   sd.be_itembrand = br.be_brandid and
   tr.be_storename = st.be_storeid and
   tr.be_transactionid = sd.be_transactionid and
   tr.createdon between '1/1/2008' and '1/1/2014'
group by 
   tr.createdon, st.be_storecountry, st.be_storelocationcategory,
   st.be_name, br.be_name
),
filtered as
(
 select *, 
 row_number() over (partition by year(createdon),month(createdon) order by total desc) rn
from cte)

select * from filtered
where rn <= 10

Upvotes: 2

Related Questions