How to choose the maximum value after SUM () + Group By?

Question

I have a college SQL final in a few days, and I have a query that drives me crazy! I know this is a stupid request, but I am just starting and I can’t understand.

So, there are basically 2 tables, Client and Orders.

Client Orders --------- --------- PK Client_Id PK Order_Id Name Client_Id FK Order_Total Date 

Now they ask me "Write off the name of the customer who bought the most in 2011"

So, for what I thought, this requires, on the one hand, that I have SUM all Order_Total and Group by Client since 2011, then from this table select the client with the sum of the sums of the sums MAX (), and then show only the name of this client. The problem is that I cannot figure out how to put all this in one request.

Hope someone can help!

---

Thank you all for your very quick answers! I am really impressed!

Now I'm not going to be picky or anything else, but just in case, my teacher does not accept the "Limit" or "Select top" instruction, is there any way to make this request without them?

Edit: attempted source code ported from comments:

 SELECT C.NAME FROM CLIENTS C, ORDERS O WHERE O.CLIENT_ID = C.CLIENT_ID AND O.DATE BETWEEN '1/1/2011 00:00:00.000' and '12/31/2011 23:59:59.999' HAVING SUM(O.ORDER_TOTAL) >= ALL (SELECT SUM (O2.ORDER_TOTAL) FROM ORDER O2 GROUP BY O2.CLIENT_ID) 

Answer 1

 SELECT TX (SELECT C.NAME X, SUM(O.ORDER_TOTAL) FROM CLIENT C, ORDERS O WHERE C.CLIENT_ID = O.CLIENT_ID AND YEAR(O.DATE) = 2011 GROUP BY O.CLIENT_ID ORDER BY 2 DESC LIMIT 1) T; 

Answer 2

There are many ways to trick this cat ... here's how I usually do it:

 select top 1 c.Client_Id, c.Name, o.Order_Total from Client c join (select Client_Id, SUM(Order_Total) as Order_Total from Orders where `Date` between '1/1/2011 00:00:00.000' and '12/31/2011 23:59:59.999' group by Client_Id) o on c.Client_Id = o.Client_Id order by o.Order_Total desc 

Basically you pull out a general list of orders, join it, sorting it in descending order, and restricting the query to 1 result.

Answer 3

You are almost there, you just need to choose from your summary request. He called the view.

So you have this:

 select c.client_id, c.name, sum(order_total) ordersum from client c inner join orders o on c.client_id = o.client_id where year(o.date) = 2011 group by c.client_id, c.name 

It gives you your amount. Now you want the first one. There are several ways to do this, and also depends on which DBMS you are using (mysql, mssql, etc.). The easiest way is as follows:

 select top 1 * from ( select c.client_id, c.name, sum(order_total) ordersum from client c inner join orders o on c.client_id = o.client_id where year(o.date) = 2011 group by c.client_id, c.name ) a order by ordersum desc 

There may be something simpler on this platform, but this request should be general enough to work with any DBMS that you use.

Answer 4

Is7aq's answer is correct according to your question, but will only work with MySQL. He also does not take into account the possibility that more than one customer bought the most this year, although I admit that this was not a limitation indicated in the original question. In addition, tremendous performance gets into cross-connects, just using commas when your databases get big enough, so it’s almost always better to use INNER or OUTER joins and set the join condition. In any case, that was an exercise, and this is what I had. It could probably be better optimized:

  CREATE TABLE #Client ( Client_Id int not null, Name varchar(100) not null ) INSERT INTO #Client VALUES (1, 'Client 1') INSERT INTO #Client VALUES (2, 'Client 2') INSERT INTO #Client VALUES (3, 'Client 3') CREATE TABLE #Orders ( Order_Id int not null, Client_Id int not null, Order_Total int not null, Date datetime not null ) -- Customer 1: total=105 INSERT INTO #Orders VALUES (1, 1, 55, '1/1/2011') INSERT INTO #Orders VALUES (2, 1, 50, '1/1/2011') INSERT INTO #Orders VALUES (3, 1, 45, '1/1/2010') -- test removal of invalid dates -- Customer 2: total=120 INSERT INTO #Orders VALUES (4, 2, 40, '1/1/2011') INSERT INTO #Orders VALUES (5, 2, 40, '1/1/2011') INSERT INTO #Orders VALUES (6, 2, 40, '1/1/2011') -- Customer 3: total=120 INSERT INTO #Orders VALUES (7, 3, 40, '1/1/2011') INSERT INTO #Orders VALUES (8, 3, 40, '1/1/2011') INSERT INTO #Orders VALUES (9, 3, 40, '1/1/2011') -- control customer to test hi/lo scenarios: total=40 INSERT INTO #Orders VALUES (10, 4, 10, '1/1/2011') INSERT INTO #Orders VALUES (11, 4, 10, '1/1/2011') INSERT INTO #Orders VALUES (12, 4, 10, '1/1/2011') INSERT INTO #Orders VALUES (13, 4, 10, '1/1/2011') SELECT T.NAME, T.OrderTotal FROM (SELECT C.NAME, SUM(O.ORDER_TOTAL) OrderTotal FROM #CLIENT C INNER JOIN #ORDERS O ON c.CLIENT_ID = o.CLIENT_ID WHERE YEAR(O.DATE) = 2011 GROUP BY C.NAME) as T WHERE T.OrderTotal = (SELECT MAX(T2.OrderTotal2) FROM (SELECT C2.NAME, SUM(O2.ORDER_TOTAL) OrderTotal2 FROM #CLIENT C2 INNER JOIN #ORDERS O2 ON c2.CLIENT_ID = o2.CLIENT_ID WHERE YEAR(O2.DATE) = 2011 GROUP BY C2.NAME) as T2) GROUP BY T.Name, T.OrderTotal DROP TABLE #Client DROP TABLE #Orders -- Output -- Client 2: 120 -- Client 3: 120 

Answer 5

 SELECT Client.Name FROM Client LEFT JOIN Orders ON Orders.Client_Id = Client.Client_Id WHERE YEAR(Orders.Date) = 2011 GROUP BY Client.Client_Id ORDER BY SUM(Order.Order_Total) DESC LIMIT 1