INVOICES TABLE
id | date | Customer
1 | 2018-01-01 | a
2 | 2018-01-01 | b
3 | 2018-01-02 | c
4 | 2018-01-02 | d
5 | 2018-01-02 | e
INVOICES_ITEMS TABLE
id | invoice_id | name | total
1 | 1 | Billing | 1500
2 | 2 | Billing | 400
3 | 2 | Reconnection | 100
4 | 3 | Installation | 1000
5 | 4 | Billing | 900
6 | 4 | Penalty | 500
7 | 5 | Reconnection | 100
I have invoice_items table in my database to store all the invoice items. i have date column in my Invoice Table and name column in my invoice_item table. What I want is to group all the data with the same name and date and get their total. I am having a hard time achieving what I want.
Expected Output:
Date | Billing | Reconnection | Installation | Penalty
2018-01-01 | 1900 | 100 | 0 | 0
2018-01-02 | 900 | 100 | 1000 | 500
Current Code:
$q = $this->db
->select ( "invoices.date,
SUM(invoices_items.total) as Billing,
SUM(invoices_items.total) as Reconnection,
SUM(invoices_items.total) as Installation,
SUM(invoices_items.total) as Penalty,
->from("invoices")
->join("invoices_items", "invoices.id=invoices_items.invoice_id")
->group_by(array("date","name"))
->get();
Current Code Output:
Date | Billing | Reconnection | Installation | Penalty
2018-01-01 | 1900 | 1900 | 1900 | 1900
2018-01-01 | 100 | 100 | 100 | 100
2018-01-02 | 900 | 900 | 900 | 900
2018-01-02 | 100 | 100 | 100 | 100
2018-01-02 | 1000 | 1000 | 1000 | 1000
2018-01-02 | 500 | 500 | 500 | 500
Try Sum with if condition ...
$q = $this->db
->select ( "invoices.date,
SUM(IF(invoices_items.name='Billing',invoices_items.total, 0)) as Billing,
SUM(IF(invoices_items.name='Reconnection',invoices_items.total, 0)) as Reconnection,
SUM(IF(invoices_items.name='Installation',invoices_items.total, 0)) as Installation,
SUM(IF(invoices_items.name='Penalty',invoices_items.total, 0)) as Penalty")
->from("invoices")
->join("invoices_items", "invoices.id=invoices_items.invoice_id")
->group_by(array("invoices.date"))
->get();