CODEWITHSUNDEEP
sqlsql-servert-sql
Wusiji
Wusiji

Reputation: 599

Taking the Largest SUM from a table

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?

Upvotes: 0

Views: 152

Answers (5)

Brad
Brad

Reputation: 645

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

Upvotes: 2

vijay
vijay

Reputation: 58

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

Upvotes: 0

Steve Kass
Steve Kass

Reputation: 7184

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

Upvotes: 1

Brett G
Brett G

Reputation: 51

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)

Upvotes: 0

Shawn
Shawn

Reputation: 3379

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.

Upvotes: 0

Related Questions