Select row that has max total value SQL Server

Question

I have the following scheme (2 tables):

Customer (Id, Name) and Sale (Id, CustomerId, Date, Sum)

How to select the following data ?

1) Best customer of all time (Customer, which has Max Total value in the Sum column)

For example, I have 2 tables (Customers and Sales respectively):

id    CustomerName      
---|--------------
1  | First         
2  | Second 
3  | Third 



id  CustomerId  datetime     Sum
---|----------|------------|-----
1  | 1        | 04/06/2013 | 50
2  | 2        | 04/06/2013 | 60
3  | 3        | 04/07/2013 | 30
4  | 1        | 03/07/2013 | 50
5  | 1        | 03/08/2013 | 50
6  | 2        | 03/08/2013 | 30
7  | 3        | 24/09/2013 | 20

Desired result:

CustomerName TotalSum
------------|--------
 First      | 150

2) Best customer of each month in the current year (the same as previous but for each month in the current year)

Thanks.

What have you tried. Do you know you need to join the tables first. Then you can Sum or aggregate the individual Sum and find the Max Value. Please take some steps to show us the effort you have already taken. Great description of the problem though. — Harrison
– Harrison, Commented Oct 16, 2013 at 20:09
Yes, of course I joined tables first. I do the following : SELECT Customer.Name, TotalSum FROM Customer INNER JOIN (SELECT Sale.CustomerId, SUM(Sum) as TotalSum FROM Sale GROUP BY Sale.CustomerId )temp ON Customer.Id = temp.CustomerId I got the result-set with two columns (CustomerName, TotalSum) and three rows (for each Customer). But how at this point to select Customer with Max TotalSum? — Sanek
– Sanek, Commented Oct 16, 2013 at 20:25

Esteban Elverdin · Accepted Answer · 2013-10-16 20:42:03Z

4

Try this for the best customer of all times

SELECT Top 1 WITH TIES c.CustomerName, SUM(s.SUM) AS TotalSum
FROM Customer c JOIN Sales s ON s.CustomerId = c.CustomerId
GROUP BY c.CustomerId, c.CustomerName
ORDER BY SUM(s.SUM) DESC

edited Oct 16, 2013 at 20:42

answered Oct 16, 2013 at 20:19

Esteban Elverdin

3,5821 gold badge20 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sgeddes Over a year ago

How does this calculate the sum? You're summing on the customer id field? Not so mention ordering by the max(customerid)... This will always return the highest customer id...

sgeddes Over a year ago

Looks better. What about ties? Assuming that's not an issue, this would be the easier solution.

Sanek Over a year ago

Thanks, but what if there are several customers with the same MaxValue of TotalSum? It probably won't be correct to get only one of them.

Esteban Elverdin Over a year ago

@user2480596 thanks for the with ties, I didn't know that it existed. Learned something today :)

Esteban Elverdin Over a year ago

@Sanek if there are more than one, "top 1 with ties" do the job.

sgeddes · Accepted Answer · 2013-10-16 20:16:12Z

1

One option is to use RANK() combined with the SUM aggregate. This will get you the overall values.

select customername,  sumtotal
from (
  select c.customername, 
    sum(s.sum) sumtotal,
    rank() over (order by sum(s.sum) desc) rnk
  from customer c
    join sales s on c.id = s.customerid
  group by c.id, c.customername
  ) t
where rnk = 1

SQL Fiddle Demo

Grouping this by month and year should be trivial at that point.

answered Oct 16, 2013 at 20:16

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Collectives™ on Stack Overflow

Select row that has max total value SQL Server

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related