SQL query to find max in a group by statement

Question

I have two tables, Customers and Lodgements. PK c.customer_ID. PK l.lodgement_Num, FK l.customer_ID.

I need to create a query which shows c.customer_fname, c.customer_sname and total lodgements made by the cutomer with highest total lodgement amount (where customers have made multiple lodgements).

My attempt:

SELECT c.customer_fname, c.customer_sname, MAX(SUM(l.lodgement_amount)) AS 
highest_total_lodgements
FROM Customers c INNER JOIN Lodgements l
ON c.customer_ID = l.customer_ID
GROUP BY c.customer_fname, c.customer_sname;

Answer 1

You seem to want just one row, that of the customer that has most "lodgements": if so, just ORDER BY and LIMIT:

SELECT c.customer_fname, c.customer_sname, SUM(l.lodgement_amount) AS lodgement_amount
FROM Customers c 
INNER JOIN Lodgements l ON c.customer_ID = l.customer_ID 
GROUP BY c.customer_id, c.customer_fname, c.customer_sname
ORDER BY lodgement_amount DESC 
LIMIT 1

Note that I added the id of the customer to the GROUP BY clause; this is safer, if ever you have two different customers with the same names.

The row-limiting syntax varies across databases. The above works in MySQL. In Oracle starting version 12, you can use the fetch clause:

ORDER BY lodgement_amount DESC 
FETCH FIRST 1 ROW ONLY

If you want to allow top ties, that's a bit different. In Oracle, use:

ORDER BY lodgement_amount DESC 
FETCH FIRST 1 ROW WITH TIES

An alternative is to use window functions:

SELECT *
FROM (
    SELECT c.customer_fname, c.customer_sname, SUM(l.lodgement_amount) AS lodgement_amount,
        RANK() OVER(ORDER BY SUM(l.lodgement_amount) DESC) rn
    FROM Customers c 
    INNER JOIN Lodgements l ON c.customer_ID = l.customer_ID 
    GROUP BY c.customer_id, c.customer_fname, c.customer_sname
) t
WHERE rn = 1