+ 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

29th Jan 2019, 10:47 PM
Meshach Griffith
Meshach Griffith - avatar
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
30th Jan 2019, 5:52 AM
CalviŐČ
CalviŐČ - avatar