I only want one maximum value but it returns all the rows [closed]

Question

I need help with this query. I'm trying to get a value for a customer that spends the most amount every month in 2018 but it doesn't work:

PROMPT 'Customer that has Spent the Most on Specific Month'
    ACCEPT MonthInput Varchar PROMPT 'Month Value:'

SELECT  E.cust_name, 
        E.cust_ID,
        MAX(P.total_amount)
FROM    customer E,
        room_booking RB,
        payment P,
        booking_details B
WHERE   P.booking_id    = RB.booking_id 
AND     RB.cust_id      = E.cust_id 
AND     B.booking_id    = RB.booking_id 
AND     B.check_in_date LIKE'%-&MonthInput-2018%' 
GROUP BY E.cust_name, E.cust_ID

My tables :

Customers (
    cust_name, 
    cust_ID
    )

room_booking (
    booking_ID,
    cust_ID*
    )

booking_details (
    booking_ID*
    )

payment (
    booking_ID*
    )

Answer 1

It returns many rows, because the MAX-function goes through every group(each customer, as you have grouped the result pr customer), and for each customer, it finds the MAX P.total_amount. I don't know how your data looks, but if each customer really just have one P.total_amount, it just prints each customers P.total_amount.

What you probably want, is something like this:

SELECT TOP 1 
    E.cust_name, 
    E.cust_ID,
    MAX(P.total_amount)
FROM    customer E,
    room_booking RB,
    payment P,
    booking_details B
WHERE   P.booking_id    = RB.booking_id 
AND     RB.cust_id      = E.cust_id 
AND     B.booking_id    = RB.booking_id 
AND     B.check_in_date LIKE'%-&MonthInput-2018%' 
GROUP BY E.cust_name, E.cust_ID
ORDER BY MAX(P.total_amount) DESC;

What we do here is the same as your original query, but we sort the list by the total amount, and selects just the first row, which must be the customer with the highest value.

If, continuing my assumption, that each customer only have one P.total_amount per month, then the grouping might not even be needed:

SELECT TOP 1 
        E.cust_name, 
        E.cust_ID,
        P.total_amount
FROM    customer E,
        room_booking RB,
        payment P,
        booking_details B
WHERE   P.booking_id    = RB.booking_id 
AND     RB.cust_id      = E.cust_id 
AND     B.booking_id    = RB.booking_id 
AND     B.check_in_date LIKE'%-&MonthInput-2018%' 
ORDER BY P.total_amount DESC;

Note that not all DBMS-systems support the TOP syntax. SQL Server does. Other systems like Postgres or MYSQL use LIMIT to a similar effect. Some DBMS also support the standard syntax OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY (SQL Server 2012+, Postgres, Oracle 12)

See wikipedia: SELECT (SQL): Limiting result rows

Answer 2

Maybe you're just missing a HAVING clause?

...
GROUP BY E.cust_name, E.cust_ID
HAVING P.total_amount = MAX(P.total_amount)
...

But I'd also recommend to rewrite the query using the explicit INNER JOIN ... ON ... syntax rather than the implicit one.

For example, instead of:

SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2

...an explicit join would read:

SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c2