Taking the Largest SUM from a table

Question

I'm trying to get the Employee with the highest sales

Employee    DeptNo  Date        Sales
Chris       2       2012/1/1    1000
Joe         1       2012/1/1    900
Arthur      3       2012/1/1    1100
Chris       2       2012/3/1    1200
Joe         1       2012/2/1    1500
Arthur      3       2010/2/1    1200
Joe         1       2010/3/1    900
Arthur      3       2010/3/1    1100
Arthur      3       2010/4/1    1200
Joe         1       2012/4/1    1500
Chris       2       2010/4/1    1800

I've tried using two subqueries, and then comparing them together to find the higher value

SELECT c1.Employee,
       c1.TOTAL_SALES
FROM  (SELECT Employee,
              Sum(sales) AS TOTAL_SALES
       FROM   EmployeeSales
       GROUP  BY Employee) c1,
      (SELECT Employee,
              Sum(sales) AS TOTAL_SALES
       FROM   EmployeeSales
       GROUP  BY Employee) c2
WHERE  ( c1.TOTAL_SALES > c2.TOTAL_SALES
         AND c1.Employee > c2.Employee )

But the resulting query gives me two rows of

Employee    TOTAL_SALES
joe         4800
joe         4800

What am I doing wrong?

score 2 · Accepted Answer · answered Jul 11 '12 at 13:09

I would use a CTE.

;With [CTE] as (
    Select
        [Employee]
        ,sum([Sales]) as [Total_Sales]
        ,Row_Number()
            Over(order by sum([sales]) Desc) as [RN]
    From [EmployeeSales]
    Group by [Employee]
)
Select
    [Employee]
    ,[Total_Sales]
From [CTE]
Where [RN] = 1

Example of working code SQL Fiddle: http://sqlfiddle.com/#!3/bd772/2

score 1 · Answer 2 · answered Jul 11 '12 at 06:02

1

To return all employees with the highest total sales, you can use SQL Server's proprietary TOP WITH TIES:

SELECT TOP (1) WITH TIES name, SUM(sales) as total_sales
FROM employees
GROUP BY name
ORDER BY SUM(sales) DESC

answered Jul 11 '12 at 06:02

Steve Kass

7,144
20
26

Shawn · Answer 3 · 2012-07-10T22:44:30.233

0

SELECT name, SUM(sales) as total_sales
FROM employees
GROUP BY name
ORDER by total_sales DESC
LIMIT 1;

A better solution is to group by an employee id so we are sure they are the same person. Since there can be two Chris's.

edited Jul 10 '12 at 22:44

answered Jul 10 '12 at 22:27

Shawn

3,583
8
46
63

Let's say for the sake of this exercise, they are the same person. Any idea where the problem would be then? – Wusiji Jul 10 '12 at 22:36
The solution i posted ^ should work for you. Does it not? Replace name with employee, etc. – Shawn Jul 10 '12 at 22:42
I only want the employee with the highest total_sales. unfortunately, yours gives all the employees with their total sales – Wusiji Jul 10 '12 at 22:43
I edited my query to only give you the top employee, however it doesn't handle the case where two employees have the same exact sales numbers. – Shawn Jul 10 '12 at 22:45

Brett G · Answer 4 · 2012-07-10T22:49:44.247

I would use a window partition

select * from
(
    select
        employee
    ,   sum(sales) as sales
    ,    row_number() over
    (
         order by sum(sales) desc
    ) as rank
    from EmployeeSales
    group by employee
) tmp
where tmp.rank = 1

And I agree with what someone said (Shawn) about having an employeeID and group by that for this, rather than the name.

(I removed the partition from the row_number() call as it is not needed for this)

score 0 · Answer 5 · answered Jul 11 '12 at 06:07

you can use CTE for that

WITH CTE AS ( select employee , sum(sales) as sales, ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sum(sales) desc) RN FROM EmployeeSales) SELECT employee , sales FROM CTE WHERE RN =1

Taking the Largest SUM from a table

5 Answers5