I have two tables
Customers
customer | customer_name |
---|---|
1 | Headley Quincey |
2 | Andie Smith |
3 | Sarah Johnson |
4 | Ernest Forrest |
Payments
payment_id | date | customer | sum_payment |
---|---|---|---|
1 | 2010-01-02 | 2 | 200 |
2 | 2011-06-06 | 3 | 500 |
3 | 2020-01-01 | 1 | 700 |
4 | 2020-02-01 | 1 | 100 |
5 | 2020-03-10 | 2 | 400 |
6 | 2020-04-08 | 3 | 500 |
7 | 2020-07-14 | 4 | 800 |
8 | 2020-09-05 | 1 | 1000 |
I need to write a query that returns all the months of 2020, the customer name and the running total for each customer. If the buyer does not have a payment in a certain month, then display "payments does not exist".
I suppose I need use CASE, but I dont know how to implement query, and how to implement the running total, and display months of 2020.
My attempt:
SELECT customer_name, SUM(sum_payment)
FROM Customers INNER JOIN
Payments ON Customers.customer=Payments.customer
GROUP BY customer_name;
>Solution :
You need to return a record for every customer by every 2020 month, so you need
- a
CROSS JOIN
between all potential month (1-12) with all customers - a
LEFT JOIN
between all these pairs with your payments table - the running sum, for which you can use the
SUM
window function
You can generate your months either by making a 11 UNION ALL
operations of months, or with a recursive query that starts from month 1 and adds one month at each iteration, stopping at 12 (I personally find the latter one more elegant).
WITH RECURSIVE months AS (
SELECT 1 AS month
UNION ALL
SELECT month + 1 FROM months WHERE month < 12
)
SELECT c.customer_name,
m.month,
SUM(p.sum_payment, 0) OVER(PARTITION BY c.customer
ORDER BY m.month) AS sum_payment
FROM months m
CROSS JOIN customers c
LEFT JOIN payments p
ON m.month = MONTH(p.date_)
AND c.customer = p.customer
AND YEAR(p.date_) = 2020
ORDER BY c.customer_name, m.month
You can skip the last ORDER BY
clause in your query: it’s just for visualization purposes.
Check the demo here.
Note: "If the buyer does not have a payment in a certain month, then display "payments does not exist".". You can’t do it. Each field is associated with one and one type only. You either need to decide whether it’s a string or an integer. I’d recommend you to leave it as integer, as it is what that field is supposed to store. If you don’t want the NULL values and prefer zeroes in place, you can change SUM(p.sum_payment)
with SUM(COALESCE(p.sum_payment, 0))
.