+ 3
SQL query for a cash report
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
1 Answer
+ 5
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