CODEWITHSUNDEEP
sqlms-access
userONE
userONE

Reputation: 23

How do I find the Name of the person with the max Monthly_sales each month?

This is what I have so far, it gets me the total for each person for each month but I need just one person each month: 

SELECT a.Month
      ,a.Name
      ,MAX(a.Monthly_sales) AS MaxOfMonthly_sales
FROM (
    SELECT mechanic_Name AS Name
          ,month(service_date) AS [Month]
          ,SUM(service_cost) AS Monthly_sales
    FROM StartingTable
    GROUP BY mechanic_name, month(service_date)) AS a
GROUP BY a.Month, a.Name

If I put a HAVING at the end it tells me it isn't part of an aggregate function and doesn't work.

Upvotes: 0

Views: 48

Answers (1)

Hamza
Hamza

Reputation: 1583

If it is returning you total of each person and you need for top one then use Group by a.Month, a.Name DESC Limit 1

Upvotes: 1

Related Questions