I'd add a subquery that returns the invoice total grouped by confirm_no and then join that to the rest of the query. So:
select reservation.confirm_no, datediff(day, res_checkin_date, res_checkout_date) * rate as basecost, invoicecost, basecost + invoicecost as totalcost
from reservation, price, room,
(select confirm_no, sum(money) as invoicecost from invoice group by confirm_no) inv
where room.price_plan = price.price_plan
and reservation.room_type = room.room_type
and inv.confirm_no = reservation.confirm_no
|