subquery shows more that one row group by

Question

I am trying to get the data for the best 5 customers in a railway reservation system. To get that, I tried getting the max value by summing up their fare every time they make a reservation. Here is the code.

SELECT  c. firstName, c.lastName,MAX(r.totalFare) as Fare
FROM    customer c, Reservation r, books b
WHERE  r.resID = b.resID
  AND  c.username = b.username
  AND  r.totalfare < (SELECT sum(r1.totalfare) Revenue
                    from Reservation r1, for_res f1, customer c1,books b1
                    where r1.resID = f1.resID
                    and   c1.username = b1.username
                    and   r1.resID = b1.resID
                    group by c1.username
      )
GROUP BY    c.firstName, c.lastName, r.totalfare
ORDER BY    r.totalfare desc
LIMIT 5;

this throws the error:[21000][1242] Subquery returns more than 1 row

If I remove the group by from the subquery the result is:(its a tabular form)

Jade,Smith,1450
Jade,Smith,725
Jade,Smith,25.5
Monica,Geller,20.1
Rach,Jones,10.53

But that's not what I want, as you can see, I want to add the name 'Jade' with the total fare.

Answer 1

I just don't see the point for the subquery. It seems like you can get the result you want with a sum()

select c.firstname, c.lastname, sum(totalfare) as totalfare
from customer c
inner join books b on b.username = c.username
inner join reservation r on r.resid = b.resid
group by c.username
order by totalfare desc
limit 5

This sums all reservations of each client, and use that information to sort the resulstet. This guarantees one row per customer.

The query assumes that username is the primary key of table customer. If that's not the case, you need to add columns firstname and lastname to the group by clause.

Note that this uses standard joins (with the inner join ... on keywords) rather than old-school, implicit joins (with commas in the from clause: these are legacy syntax, that should not be used in new code.