SQL Server SQL query to find the highest total sales in region by which representative [duplicate]

41zrol4v 于 2023-05-16 发布在其他

关注(0)|答案(2)|浏览(98)

This question already has answers here:

Get top 1 row of each group (19 answers)
Closed 4 days ago.

I have the following data set:

CREATE TABLE OrderHistory 
(
    OrderDate DATETIME, 
    Representative NVARCHAR(20), 
    Region NVARCHAR(10), 
    UnitSales INT
);

INSERT INTO OrderHistory 
VALUES 
('2014-09-01', 'Smith',    'Central', 2),
('2015-06-17', 'Kivell',   'Central', 5),
('2015-09-10', 'Gill',     'Central', 7),
('2015-11-17', 'Jardine',  'Central', 11),
('2015-10-31', 'Andrews',  'Central', 14),
('2014-02-26', 'Gill',     'Central', 27),
('2014-10-05', 'Morgan',   'Central', 28),
('2015-12-21', 'Andrews',  'Central', 28),
('2014-02-09', 'Jardine',  'Central', 36),
('2015-08-07', 'Kivell',   'Central', 42),
('2015-01-15', 'Gill',     'Central', 46),
('2014-01-23', 'Kivell',   'Central', 50),
('2015-03-24', 'Jardine',  'Central', 50),
('2015-05-14', 'Gill',     'Central', 53),
('2015-07-21', 'Morgan',   'Central', 55),
('2015-04-10', 'Andrews',  'Central', 66),
('2014-12-12', 'Smith',    'Central', 67),
('2014-04-18', 'Andrews',  'Central', 75),
('2015-05-31', 'Gill',     'Central', 80),
('2015-02-01', 'Smith',    'Central', 87),
('2014-05-05', 'Jardine',  'Central', 90),
('2014-06-25', 'Morgan',   'Central', 90),
('2015-12-04', 'Jardine',  'Central', 94),
('2014-11-25', 'Kivell',   'Central', 96),
('2015-02-18', 'Jones',    'East',    4),
('2014-11-08', 'Parent',   'East',    15),
('2014-09-18', 'Jones',    'East',    16),
('2014-07-12', 'Howard',   'East',    29),
('2014-08-15', 'Jones',    'East',    35),
('2014-04-01', 'Jones',    'East',    60),
('2014-06-08', 'Jones',    'East',    60),
('2015-07-04', 'Jones',    'East',    62),
('2014-10-22', 'Jones',    'East',    64),
('2014-12-29', 'Parent',   'East',    74),
('2014-07-29', 'Parent',   'East',    81),
('2014-01-06', 'Jones',    'East',    95),
('2015-04-27', 'Howard',   'East',    96),
('2015-08-24', 'Sorvino',  'West',    3),
('2015-03-07', 'Sorvino',  'West',    7),
('2014-05-22', 'Thompson', 'West',    32),
('2014-03-15', 'Sorvino',  'West',    56),
('2015-10-14', 'Thompson', 'West',    57),
('2015-09-27', 'Sorvino',  'West',    76);

Ignoring the date, how can I write a query to answer the following question?

Who is the top representative in each of the region by number of unit sales?

Expected output: Representative, Region, TotalUnitsSoldByHim

Here's what I've tried so far:

SELECT
    Region, MAX(all_units) AS Max_Units, 
FROM
    (SELECT DISTINCT
         t1.Region, t1.Rep, 
         t2.total_units AS all_units
     FROM 
         SALES t1
     JOIN 
         (SELECT
              Rep, SUM(Units) AS total_units
          FROM 
              SALES 
          GROUP BY
              Rep) t2 ON t1.Rep = t2.Rep) temp 
GROUP BY
    Region

sql-server

来源：https://stackoverflow.com/questions/76230934/sql-query-to-find-the-highest-total-sales-in-region-by-which-representative

2条答案

按热度按时间

yqkkidmi1#

This might be a good spot for WITH TIES , which makes for a shorter query:

select top (1) with ties rep, region, sum(units) as total_units
from mytable t
group by rep, region
order by row_number() over(partition by region order by sum(units) desc)

赞(0）回复(0）举报 2023-05-16

jaql4c8m2#

You can use the window ROW_NUMBER() to get the best sales person per region

WITH numb AS(
SELECT
    Representative , 
    Region , 
    SUM(UnitSales) salestotal,
  ROW_NUMBER() OVER(PARTITION BY region ORDER by SUM(UnitSales) DESC) rn
FROM OrderHistory
GROUP BY     Representative , 
    Region)
SELECT
    Representative , 
    Region , 
    salestotal
FROM numb WHERE rn = 1

Representative	Region	salestotal
Jardine	Central	281
Jones	East	396
Sorvino	West	142

fiddle

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server SQL query to find the highest total sales in region by which representative [duplicate]

2条答案

相关问题

热门标签

最新问答