CODEWITHSUNDEEP
mysqlcodeigniter
Hamza Zahir
Hamza Zahir

Reputation: 69

Codeigniter group by if a field is not null only

I have the following query:

$this->db->select('SUM(cost_price) AS cost_price,SUM(cost_price) AS total,employees.username AS staff_name');
        $this->db->from('items');
        $this->db->join('customers', 'customers.person_id = items.customer_id');
        $this->db->join('employees', 'employees.person_id = items.staff_id');
        $this->db->join('people', 'people.person_id = customers.person_id');
 $this->db->group_by('items.is_serialized');

In the items table I have a field called is_serialized, I want to group all rows having a value in the is_serialized field but not group if the row is NULL, would it be possible to do so using my query?

Upvotes: 1

Views: 874

Answers (2)

Lalit Giriya
Lalit Giriya

Reputation: 312

Use "CASE" function in group by clause to check for not null values only.

Eg: 

 $this->db->group_by("CASE WHEN items.is_serialized IS NOT NULL THEN items.is_serialized END",FALSE);

Upvotes: 3

Kirtee
Kirtee

Reputation: 141

Put below where condition to be group by is_serialized

$this->db->where('items.is_serialized IS NOT NULL');

Upvotes: 0

Related Questions