Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I select the max value after a SUM() + Group By?

Tags:

sql

group-by

sum

I have an SQL final exam in college in a few days and I have a query that's driving me crazy! I know it's a dumb query, but I'm just getting started and can't figure it out.

So, there's basicaly 2 tables, Client and Orders.

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

Now, they ask me to "List the name of the client that bought the most in 2011"

So, for what I thought, this requires on one side, that I SUM all the Order_Total and Group by Client from 2011, then from that table, select the client with the MAX() sum of order totals, and then show only the name of that client. The problem is that I can't figure how to put all that in one query.

Hope somebody can help!


Thank you all for your very quick responses! I'm really impressed!

Now, I don't mean to be picky or anything, but just in case my teacher doesn't accept the "Limit" or "Select top" statement, is there any way to do this query without those?

Edit: Original code attempt 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)
like image 497
nachoargentina Avatar asked Feb 25 '12 05:02

nachoargentina


People also ask

Can Max function be used in GROUP BY?

The MAX() is an aggregate function, so it can be used in Group By queries. The following query gets highest salary in each department.

How do you SELECT the highest value in each group in SQL?

How do you get max for each group in SQL? To find the maximum value of a column, use the MAX() aggregate function; it takes a column name or an expression to find the maximum value. In our example, the subquery returns the highest number in the column grade (subquery: SELECT MAX(grade) FROM student ).

How do you SUM and Max in the same query?

SUM() and MAX() at the same time Notice that all aggregate functions except COUNT(*) ignore the NULL Rating for the ID=5 row. COUNT(*) counts rows, whereas COUNT(col) counts non-null values. So to answer your question, just go ahead and use SUM() and MAX() in the same query.

How do you SELECT the highest value?

To find the max value of a column, use the MAX() aggregate function; it takes as its argument the name of the column for which you want to find the maximum value. If you have not specified any other columns in the SELECT clause, the maximum will be calculated for all records in the table.


1 Answers

SELECT T.X
 (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;
like image 127
Isaac Avatar answered Oct 06 '22 01:10

Isaac