SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Invoices (`Amount` int); INSERT INTO Invoices (`Amount`) VALUES (1012), (1055), (1055), (1200), (1265), (1270); CREATE TABLE Payments (`Amount` int); INSERT INTO Payments (`Amount`) VALUES (1055), (1200), (1265); SELECT * FROM Invoices; SELECT * FROM Payments; SELECT Invoices.amount, COALESCE(COUNT(Invoices.amount),0) AS invoices_count, COALESCE(x.payments_count,0) AS payments_count FROM Invoices LEFT JOIN ( SELECT Payments.amount, COUNT(Payments.amount) AS payments_count FROM Payments GROUP BY Payments.amount ) AS x ON Invoices.amount = x.amount GROUP BY Invoices.amount UNION SELECT Payments.amount, COALESCE(x.invoices_count,0) AS invoices_count, COALESCE(COUNT(Payments.amount),0) AS payments_count FROM Payments LEFT JOIN ( SELECT Invoices.amount, COUNT(Invoices.amount) AS invoices_count FROM Invoices GROUP BY Invoices.amount ) AS x ON Payments.amount = x.amount GROUP BY Payments.amount;