CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecord
Gulmuhammad Akbari
Gulmuhammad Akbari

Reputation: 2036

CodeIgniter active record count mysql records according value

What would be wrong with my active record query the SUM function works fine but the count parts returns the count of all records.

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

enter image description here

What I want the count_sale should be 3 and count_buy should be 4:

enter image description here

Upvotes: 0

Views: 50

Answers (2)

Rohit Dhiman
Rohit Dhiman

Reputation: 2741

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();

Upvotes: 0

Rakesh Sojitra
Rakesh Sojitra

Reputation: 3658

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();

Upvotes: 1

Related Questions