I am left joining 4 tables to get SUM of the amount that is present in first table. So far, I've tried many alternatives to get the sum straight, but either it becomes 0 (if I group_by), or duplicates, if I don't use group_by. here is my code:
$this->db->select('COALESCE(SUM(cust_ reservation_request.dollar_amount), "0") as total'); $this->db->join('cust_ reservation_request', 'cust_ reservation_request.id = cust_reservations.rid', 'LEFT'); $this->db->join('attendees', 'cust_rid= cust_ reservation_request.id', 'LEFT'); $this->db->join('cust_reservation_cancelled', 'cust_reservation_cancelled.rid = cust_ reservation_request.id', 'LEFT'); $this->db->where('cust_ reservation_request.tid IN (5, 6, 7, 8)->where('attendees.status', 1)->where('cust_reservation_cancelled.rid IS NULL'); $this->db->group_by('cust_reservation_request.id'); $total = $this->db->get('cust_reservations')->row()->total;
I am getting 0 as a value of $total variable, while it should be 288. However, when I remove
$this->db->group_by('cust_reservation_request.id'); statement, I get duplicate on
attendees.attendees.cust_rid, which is actually there, $total becomes 498, since there can be more than one attendees for the same rid. I want to remove this duplicate, retaining actual SUM of all the dollar_amount fields.
My tables are:
cust_ reservation_request id // index, foreign key in rest of other tables tid uid dollar_amount cust_reservations id rid // related to id in cust_ reservation_request table reservation_date pay_token cust_ reservation_cancelled id rid // related to cust_ reservation_request.id cancel_date attendees id cust_rid // its the same rid, related to cust_reservation_request.id status
Do you think there can be any safer way to do this, removing duplicates while doing the SUM in a proper way?
Thank you in advance for any help.
My original answer was: "I think you have problem with your data and the query returns NULL which is converted into 0 by COALESCE", but it wasn't the issue. There was a problem with "status" field in "attendees" table and it was solved mainly by topic starter, so I do not deserve points here
Zafar, happy you managed to solve the problem. Greetings from Israel :)