Group SQL PHP data with sum of records

Question

I have the following appointment:

    $this->db->select('tbl_supplier.supplier_name, tbl_order_details.*');
    $this->db->join("tbl_supplier", "tbl_supplier.supplier_id=tbl_order_details.supplier_id");
    $this->db->where('tbl_order_details.order_id', $id);
    $this->db->get('tbl_order_details')->result();

My return in SQL is as follows:

SELECT `tbl_supplier`.`supplier_name`, `tbl_order_details`.*
FROM (`tbl_order_details`)
JOIN `tbl_supplier` ON `tbl_supplier`.`supplier_id`=`tbl_order_details`.`supplier_id`
WHERE `tbl_order_details`.`order_id` =  '6'

And in the database returns me the following:

It works as it should, however, I need that in this listing I can group by product and add quantity and sub_total.

BS: I may have different products in this listing, not necessarily a single type.

Expected result:

Dell i5 Notebook | 15 units | Total R$ 20,464.29

Answer 1

Just enter the sum at the beginning of your select by grouping by the product code. See:

SELECT `tbl_order_details`.`product_name`, 
    sum(`tbl_order_details`.`product_quantity`) as unidades, # soma de quantidades
    sum(`tbl_order_details`.`subtotal`) as total # soma de valores
FROM `tbl_order_details`
JOIN `tbl_supplier` ON `tbl_supplier`.`supplier_id`=`tbl_order_details`.`supplier_id`
WHERE `tbl_order_details`.`order_id` =  '6' 
GROUP BY `tbl_order_details`.`product_code`; # agrupar por código do produto

See working on SQL Fiddle

In Codeigniter:

    $this->db->select(   // somas
        'tbl_order_details.product_name, 
        sum(tbl_order_details.product_quantity), 
        sum(tbl_order_details.subtotal)'
    );
    $this->db->join("tbl_supplier", "tbl_supplier.supplier_id=tbl_order_details.supplier_id");
    $this->db->where('tbl_order_details.order_id', $id);
    $this->db->group_by('tbl_order_details.product_code'); // agrupamento
    $this->db->get('tbl_order_details')->result();