Running total for each customer for a certain time period

Question

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;

lemon · Accepted Answer · 2023-05-20T23:35:05.117

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)).

score 1 · Answer 2 · answered May 20 '23 at 23:59

You can first generate the months with a recursive cte, and then join the payments and customers onto it, computing the sum for each customer and month. If no sum exists for a given month-customer pair, 'payments do not exists' can be included instead:

with recursive cte(d) as (
   select cast('2020-01-01' as date)
   union all
   select c.d + interval 1 month from cte c where extract(month from c.d) < 12
)
select t.d, c.customer_name, case when t.s is null then 'payments do not exists' else t.s end 
from (
    select c.d, c1.customer, sum(p.sum_payment) s from cte c 
    cross join customers c1 
    left join payments p on p.customer = c1.customer and year(c.d) = year(p.date) and month(c.d) = month(p.date)
    group by c.d, c1.customer) t
join customers c on c.customer = t.customer
order by t.customer, t.d

See fiddle

I would not recommend a solution that casts a numeric field to string, whereas a null value would be more appropriate... would also translate to a bad habit. Nice the translation of months to dates tho. — lemon, May 21 '23 at 00:06

Running total for each customer for a certain time period

2 Answers2