How to conditionally COUNT() aggregate row data with CodeIgniter's query builder

Question

What would be wrong with my active record query? The SUM function works fine, but the COUNT() parts return the count of all records (like the conditional expression is always true).

$this->db->select('
    t1.account_balance, 
    t2.CustomerName AS customer,
    t2.CustomerId,
    SUM(IF(t3.Bargain="Sale",t3.TotalAmount,0)) AS total_sale,
    SUM(IF(t3.Bargain="Purchase",t3.TotalAmount,0)) AS total_buy,
    COUNT(IF(t3.Bargain="Sale",t3.Bargain,0)) AS count_sale,
    COUNT(IF(t3.Bargain="Purchase",t3.Bargain,0)) AS count_buy,
    ', FALSE);
$this->db->from("balances AS t1");
$this->db->join("customer AS t2","t2.CustomerId = t1.customer_id","left");
$this->db->join("gold_order AS t3","t3.CustomerId = t2.CustomerId","left");
$this->db->group_by("t3.CustomerId");
$object = $this->db->get();

Result:

I want the count_sale to be 3 and the count_buy to be 4.

Rakesh Sojitra · Accepted Answer · 2016-08-31 06:50:19Z

1

try this

$this->db->select(
                    '
                     t1.account_balance, 
                     t2.CustomerName AS customer,
                     t2.CustomerId,
                     SUM(IF(t3.Bargain="Sale",t3.TotalAmount,0)) AS total_sale,
                     SUM(IF(t3.Bargain="Purchase",t3.TotalAmount,0)) AS total_buy,
                     SUM(IF(t3.Bargain="Sale",1,0)) AS count_sale,
                     SUM(IF(t3.Bargain="Purchase",1,0)) AS count_buy,
                     '
                 ,FALSE);
$this->db->from("balances AS t1");
$this->db->join("customer AS t2","t2.CustomerId = t1.customer_id","left");
$this->db->join("gold_order AS t3","t3.CustomerId = t2.CustomerId","left");
$this->db->group_by("t3.CustomerId");
$object = $this->db->get();

answered Aug 31, 2016 at 6:50

Rakesh Sojitra

3,6682 gold badges19 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rohit Dhiman · Accepted Answer · 2016-08-31 07:02:05Z

Try this:

$this->db->select('
                         t1.account_balance, 
                         t2.CustomerName AS customer,
                         t2.CustomerId,
                         SUM(IF(t3.Bargain="Sale",t3.TotalAmount,0)) AS total_sale,
                         SUM(IF(t3.Bargain="Purchase",t3.TotalAmount,0)) AS total_buy,
                        (SELECT count(*) from gold_order where gold_order.Bargain="Sale") AS count_sale,
                         (SELECT count(*) from gold_order where gold_order.Bargain="Purchase") AS count_buy,
                         '
                     ,FALSE);
    $this->db->from("balances AS t1");
    $this->db->join("customer AS t2","t2.CustomerId = t1.customer_id","left");
    $this->db->join("gold_order AS t3","t3.CustomerId = t2.CustomerId","left");
    $this->db->group_by("t3.CustomerId");
    $object = $this->db->get();

mickmackusa · Accepted Answer · 2025-09-17 09:02:23Z

COUNT() will increase the tally by 1 for every non-NULL value that it is fed. You condition inside of COUNT() never supplies a NULL value, so the result is incorrectly counted.

More simply, MySQL's SUM() will happily receive a boolean evaluation -- provide a comparison expression instead of a ternary.

CodeIgniter's select_sum() will disable automatic identifier quoting if the passed in text contains a parenthesis or a single quote.

You can pass a single column name as the 2nd parameter of join() when the relating columns are identical -- this renders USING (`columnName`).

public function goldOrderSummaryByCustomer(): array
{
    return $this->db
        ->select([
            't1.account_balance', 
            't2.CustomerName customer',
            't2.CustomerId',
        ])
        ->select_sum("IF(t3.Bargain='Sale',t3.TotalAmount,0)", 'total_sale')
        ->select_sum("IF(t3.Bargain='Purchase',t3.TotalAmount,0)", 'total_buy')
        ->select_sum("t3.Bargain='Sale'", 'count_sale')
        ->select_sum("t3.Bargain='Purchase'", 'count_buy')
        ->from('balances t1')
        ->join('customer t2', 'CustomerId', 'left')
        ->join('gold_order t3', 'CustomerId', 'left')
        ->group_by('t2.CustomerId')
        ->get()
        ->result();
}

Collectives™ on Stack Overflow

How to conditionally COUNT() aggregate row data with CodeIgniter's query builder

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related