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

more