Multi tool use

LEFT JOIN - Return empty results with left join for sum of payment_method

This is my first question on stackoverflow so I try my best to not screw up. I am scratching my head on this query for a couple of hours and can't make any progress.

I use a view which has several bookings in it. All those bookings have a payment_method e.g. paypal or creditcard.

Customer Name | Price | payment_method
John Doe | 20 | creditcard
Susan Soe | 10 | paypal


With my sql query I am trying to get the sums for all payment_methods. Unfortunately payment_methods which were not used this month don't show up.

So I created an additional table called PaymentMethods with the column Methods.

Methods
creditcard
premium
sofort
bank
paypal


I then tried to use a LEFT JOIN to get a result like this:

LEFT JOIN

payment_method | TotalQuantity (->sum(price))
creditcard | 20
premium | 0
sofort | 0
bank | 0
paypal | 10


But instead I only get those sums returned which have a payment_method used that month already.

payment_method | TotalQuantity (-> sum(price))
creditcard | 20
paypal | 10


This is my statement. I would really appreciate some input on what I do wrong here.

SELECT payment_method, SUM(IFNULL(price,0)) AS TotalQuantity
FROM PaymentMethods
LEFT JOIN PaymentMethods
ON SlotBookingsFullView.payment_method=PaymentMethods.Methods
WHERE (booking_date BETWEEN '2018-07-01 00:00:00' AND '2018-07-30 23:59:59')


* EDIT *
m0lochwalker pointed out that I should use GROUP BY clause...I had it but it got during copy&paste action. Here it is.

GROUP BY

GROUP BY PaymentMethods.Methods ORDER BY PaymentMethods.Methods DESC


FROM PaymentMethods LEFT JOIN PaymentMethods

FROM PaymentMethods LEFT JOIN SlotBookingsFullView

2 Answers
2

When you outer join records, their values are null:

So with

WHERE (SlotBookingsFullView.booking_date BETWEEN '2018-07-01 00:00:00'
AND '2018-07-30 23:59:59')


you are dismissing all outer-joined rows, because their booking_date is not in the desired date range, but null obviously. This renders your join a mere inner join. Put criteria on outer-joins in their ON clause instead:

booking_date

ON

SELECT pm.Methods, COALESCE(SUM(b.Price), 0) AS TotalQuantity
FROM PaymentMethods pm
LEFT JOIN SlotBookingsFullView b
ON b.payment_method = pm.Methods
AND b.booking_date >= date '2018-07-01'
AND b.booking_date < date '2018-07-31'
GROUP BY pm.Methods
ORDER BY pm.Methods DESC;


(As you see, COALESCE or IFNULL belongs outside of SUM not inside it, because if the sum is null, you want to replace it with zero.)

COALESCE

IFNULL

SUM

Your result is what a LEFT JOIN would do. You should SUM TotalQuantity and Group By payment_method. Have you looked at the Group By clause? Something like:

SELECT payment_method, sum(totalquantity) as qty
FROM myTable
GROUP BY payment_method


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.