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

我的数据库中有invoice\u items表来存储所有发票项。我有 date column 在我的 Invoice Table 以及 name column 在我的 invoice_item table . 我想要的是将所有具有相同名称和日期的数据分组，得到它们的总数。我很难实现我想要的。任何帮助都将不胜感激。谢谢
预期产量：

Date   | Billing | Reconnection | Installation | Penalty
2018-01-01 |  1900   |   100        |      0       |    0
2018-01-02 |  900    |   100        |      1000    |    500

当前代码：

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

当前代码输出：

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