SQL query for a cash report

Question

Can someone help me write a query to do the following ?

Table : Shipments

Id created_by total payment_type date
1 Tim 50 Cash 2019-01-01
2 Tim 30 Linx 2019-01-01
3 Joyce 40 Cash 2019-01-01

Report
2019-01-01

User Cash Linx Total
Tim 50 30 80
Joyce 40 0 40

......
Please ? I would really appreciate this

Answer 1

SELECT * FROM Shipments;

SELECT @Report := '2019-01-01';

SELECT T_total.User, T_Cash.Cash, T_Linx.Linx, T_total.Total, T_total.date
FROM
(
SELECT created_by AS User, SUM(total) AS Total, date
FROM Shipments
WHERE date = @Report
GROUP BY created_by
) AS T_total
LEFT OUTER JOIN
(
(
SELECT created_by AS User, SUM(total) AS Cash
FROM Shipments
WHERE date = @Report and payment_type = 'Cash'
GROUP BY created_by
) AS T_Cash
) ON T_total.User = T_Cash.User
LEFT OUTER JOIN
(
(
SELECT created_by AS User, SUM(total) AS Linx
FROM Shipments
WHERE date = @Report and payment_type = 'Linx'
GROUP BY created_by
) AS T_Linx
) ON T_total.User = T_Linx.User
ORDER BY T_total.User DESC;

Tested on http://sqlfiddle.com/#!9/1c02b5/1