I'm new to SQL, coming from Python, and having a hard time understanding SQL syntax. I have one table called "Purchases", and a second table, below the first, called "Customers".
I want to SELECT the LastName of the Customer with the most Purchases (which can be counted in PurchaseID). I have figured out how to aggregate Purchase count by Last Name, but I can't figure out how to return the single Customer LastName with the most purchases. Lastly, I'm not sure if I'm doing this in the most intuitive way? I'm happy to receive feedback.
| PurchaseID | CustomerID |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 5 |
| 7 | 5 |
| 8 | 4 |
| 9 | 3 |
| 10 | 2 |
| LastName | CustomerID |
|---|---|
| Davis | 1 |
| Smith | 2 |
| Tran | 3 |
| Washington | 4 |
| Lopez | 5 |
SELECT t2.LastName, COUNT(t1.CustomerID) AS purchase_count
FROM Purchases t1
LEFT JOIN Customers t2 ON
t1.CustomerID = t2.CustomerID
GROUP BY t2.LastName
returns
| LastName | purchase_count |
|---|---|
| Davis | 1 |
| Smith | 2 |
| Lopez | 3 |
| Tran | 2 |
| Washington | 2 |
CodePudding user response：
One solution is using ORDER BY purchase_count DESC LIMIT 1 at the end of your query as mentioned. However, it will return always just one customer even if there are more customers with the highest purchase count.
If you would like to return all customers with the highest purchase count then use the following query.
SELECT t2.LastName, COUNT(t1.CustomerID) AS purchase_count
FROM Purchases t1
LEFT JOIN Customers t2
ON t1.CustomerID = t2.CustomerID
GROUP BY t2.LastName
HAVING COUNT(t1.CustomerID) >= all(
SELECT COUNT(t1.CustomerID) AS purchase_count
FROM Purchases t1
GROUP BY t1.CustomerID
)