0

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*
    )

closed as unclear what you're asking by Tom V, Andriy M, mustaccio, Colin 't Hart, Erik Darling Aug 8 '18 at 18:56

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    What database are you using? – Marco Aug 7 '18 at 6:54
  • 2
    Whad do you mean by "doesn't work"? – Tom V Aug 7 '18 at 8:19
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

0

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.