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?
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)
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 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 ).
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.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With