how to select multiple sum() quantity

i want to select the sum of quantity(qty) from orders table,there are 2 category dirham (kupon_id = 2) and dinar (kupon_id = 1)

i already got the sum of kupon_id = 2,but i dont know how to get sum of qty where kupon_id = 1,here the example :

my model :

$this->db->join('
(
  SELECT sum(co.qty) as total_dirham,co.user_id
  from ci_order co 
  where co.kupon_id = 2 group by co.user_id
  ) as co
','co.user_id=cug.user_id','left');

i tried join the same table with difference WHERE condition but its wrong :

  $this->db->join('
(
  SELECT sum(co.qty) as total_dirham,co.user_id
  from ci_order co 
  where co.kupon_id = 2 group by co.user_id
  ) as co
','co.user_id=cug.user_id','left');
  $this->db->join('
(
  SELECT sum(co.qty) as total_dinar,co.user_id
  from ci_order co 
  where co.kupon_id = 1 group by co.user_id
  ) as co
','co.user_id=cug.user_id','left');

update model :

$this->db->join('
(
  SELECT sum(co.qty) as total,co.user_id, co.kupon_id
  from ci_order co 
  group by co.user_id, co.kupon_id
  ) as co
  
','co.user_id=cug.user_id','left');

the result shows both quantity of the id at difference row,i want it to show at Dinar,example like this :

Answer

Try this:

$this->db->join('
(
  SELECT 
    (select sum(qty) from ci_order where kupon_id = 2 and user_id = a.user_id) as total_dirham, 
    (select sum(qty) from ci_order where kupon_id = 1 and user_id = a.user_id) as total_dinar,
    a.user_id
  from ci_order a
) as co
','co.user_id=cug.user_id','left');

Advertisement

Answer