CODEWITHSUNDEEP
mysqlsql
Rogan Farrira
Rogan Farrira

Reputation: 15

In SQL or mySQL, how can I determine the highest sum of a someone with an associated key?

enter image description here

if I have the table above, waiter 1102 has the most tips, because he makes $70 tips in total. But in SQL, how can I write a query that find the waiter who makes the most tips. In this case, I want to find waiter 1102 who makes the most tips.

Upvotes: 1

Views: 71

Answers (4)

DineshDB
DineshDB

Reputation: 6193

In MySQL:

SELECT Waiter, sum(tips)Tips
FROM T
GROUP BY waiter
ORDER BY sum(tips) DESC
LIMIT 1

In SQL Server:

SELECT TOP 1 Waiter, sum(tips)Tips
FROM T
GROUP BY waiter
ORDER BY sum(tips) DESC

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

If you want to retrieve all waiters who have the largest tip amount, including all waiters who are tied, then you may try the following query:

SELECT WaiterID, SUM(Tips) AS total_tips
FROM yourTable
GROUP BY WaiterID
HAVING SUM(Tips) = (SELECT MAX(Tips) FROM (SELECT SUM(Tips) AS Tips
                                           FROM yourTable
                                           GROUP BY WaiterID) t);

You could also use the LIMIT trick here. But that would not return all ties in MySQL.

Upvotes: 2

Vijayakumar
Vijayakumar

Reputation: 338

This will give you the results you've asked for

1. select sum(tips),waiterid from tablename group by waiterid

If you want to sort by highest sum then sort by tips

2. select sum(tips),waiterid from tablename group by waiterid sort by tips desc

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can use group by and limit:

select waiter, sum(tips)
from t
group by waiter
order by sum(tips) desc
limit 1;

Upvotes: 1

Related Questions