How to get Highest Sales Person in Month for Monthly Reward

Abdul Amin Khan

7y2.7k

Dear All,

I have a SQL question not able to get exact record.

How to get Monthly Highest Sales Person in given record.

Please tell me where I am wrong.

Employee Table:

Sales Table:

Expected Result:

Tried Query:

With Temp AS(

Select e.empID,SUm(s.Sale) as Sale,s.monthname

From tblEMp e inner join tblSales s on e.empID=s.empID

Group by s.monthname,e.empiD)

Select Distinct(Sale),empID,monthName from Temp Where Sale in (Select Max(Sale) from Temp Group by monthName)

Group by monthName,empID,sale

order by monthname

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty7y ago
Accepted answer
Hey try this
With Temp1 AS(
SELECT empid,SUM(sale) as sale, month from tblSales
group by empid,month)
SELECT empid,sale,month from (select t.*,
               row_number() over (partition by t.month order by t.sale desc) as rn
          from Temp1 t) x
where rn = 1
+3
Abdul Amin Khan7y ago
Thank You Amit Sir. You are genious. I forgot to use row_number().
+1