How to get the top performing employees from each Region?-CodePudding

I have a table Sales with three columns: Sales_Rep: The name of the sales representative, Region: denoting the region they work in and Total_sales: Total Sales done by the sales representative. I want to find only the sales reps with maximum sales in each region. The table has 50 rows.
I tried doing it using self join but no rows are returned.
The SQl code which I have written is:

SELECT s1.Sales_Rep, s1.Region,s1.Total_sales
FROM sales s1
JOIN sales s2
ON s1.Sales_Rep = s2.Sales_Rep
WHERE s1.Region = s2.Region 
AND s1.Total_sales > s2.Total_sales

Can anyone please tell me how to solve this?The table image for reference

CodePudding user response：

I used a CTE to get the max sales of each region then joined it back to the original table based on the total sales and region to find the saleperson

WITH MAX_CTE
AS
(
SELECT
    REGION,
    MAX(TOTAL_SALES) AS TOTAL_SALES
FROM SALES 
GROUP BY REGION
)
SELECT
    *
FROM SALES TABLE1
    JOIN MAX_CTE CTE1 ON CTE1.REGION = TABLE1.REGION AND CTE1.TOTAL_SALES = TABLE1.TOTAL_SALES

CodePudding user response：

This could work

select Sales_Rep, Region, max(Total_sales)
from sales
group by Region

you group by Region and select the max from Total_sales

CodePudding user response：

Using a Where Statement with another Select statement, you'll be sure to get the highest total sales. By using distinct(Region) and Group by Region. The values will be seperated by Region so each will have its own Sales Rep with the highest Total sales.

    Select Sales_Rep, Region, Total_sales
    FROM sales
    Where Total_sales = (Select max(Total_sales) from sales) 
    GROUP by Region;

Edited: The where statement should be stated before the group statement
Edited2 : Removed Distinct from Select statement