[SOLVED] Math On Result of Subquery

[JonnoTheDev]

Having difficulty with this query. What I have is a booking system where a user can be assigned to a booking (many bookings). There is a comission rate for each booking that is to be paid to the user. The administrator can see the amount owed per booking and make payments, however they can pay in parts i.e 50 - 2 installments of 25.

 

What I need is the total amount in comission minus the amount paid for all bookings giving the total owed to the user.

 

tables

 

bookings

========

bookingId

userId

price

rate

payMethod

 

user

========

userId

name

 

collection

========

id

bookingId

amount

 

 

Query:

SELECT u.userId, u.name, SUM(b.rate), (SELECT SUM(amount) FROM collection c WHERE b.bookingId=c.bookingId) 

FROM users u INNER JOIN bookings b ON (u.userId=b.userId) 

WHERE b.payMethod='cash' GROUP BY u.userId ORDER BY u.name ASC

 

This gives the correct information but I need to minus the results of (SELECT SUM(amount) FROM collection c WHERE b.bookingId=c.bookingId) from SUM(b.rate).

A simple minus operator gives me a null result.

[jackpf]

SELECT u.userId, u.name, SUM(b.rate) -  SUM(z.amount) AS `Total`

FROM users u INNER JOIN bookings b ON (u.userId=b.userId)
INNER JOIN collection z ON b.bookingId=c.bookingId

WHERE b.payMethod='cash' GROUP BY u.userId ORDER BY u.name ASC

Can't you just do that?

Then `Total` will contain the total amount. Possibly...

 

I have no idea if that'll work.

[JonnoTheDev]

I cant inner join the collection table as it may contain 0 records for some bookings i.e the sum of b.rate is still owed - no collections have been made by the user

[luca200]

Try this way

SELECT u.userId, u.name, SUM(b.rate) - (ifnull (SELECT SUM(amount) FROM collection c WHERE b.bookingId=c.bookingId), 0 )

FROM users u INNER JOIN bookings b ON (u.userId=b.userId)

WHERE b.payMethod='cash' GROUP BY u.userId ORDER BY u.name ASC

Not tested...

[JonnoTheDev]

no, just gives a query error near the subquery. I may just put the collection total in the same table as the bookings as opposed to logging each individual collection

[luca200]

no, just gives a query error near the subquery.

...which?

[JonnoTheDev]

At this point (ifnull (SELECT SUM(amount) FROM

[luca200]

You'd better post the EXACT error message

[JonnoTheDev]

Thanks, used an alternate solution

[fenway]

Thanks, used an alternate solution

That's great!  So post it here for everyone else's benefit.

[JonnoTheDev]

Oh, its nothing fancy - I mentioned it above

I may just put the collection total in the same table as the bookings as opposed to logging each individual collection