CREATE TABLE invoices (
id INT PRIMARY KEY,
date DATE,
customer VARCHAR(50)
);
CREATE TABLE invoices_items (
id INT PRIMARY KEY,
invoice_id INT,
name VARCHAR(50),
total INT,
FOREIGN KEY (invoice_id) REFERENCES invoices(id)
);
INSERT INTO invoices (id, date, customer) VALUES
(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');
INSERT INTO invoices_items (id, invoice_id, name, total) VALUES
(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);
SELECT
i.date,
SUM(CASE ii.name WHEN 'Billing' THEN ii.total ELSE 0 END) Billing,
SUM(CASE ii.name WHEN 'Reconnection' THEN ii.total ELSE 0 END) Reconnection,
SUM(CASE ii.name WHEN 'Installation' THEN ii.total ELSE 0 END) Installation,
SUM(CASE ii.name WHEN 'Penalty' THEN ii.total ELSE 0 END) Penalty
FROM
invoices i
JOIN
invoices_items ii ON i.id = ii.invoice_id
GROUP BY
i.date
ORDER BY
i.date