CODEWITHSUNDEEP
sql
SteveNashCash
SteveNashCash

Reputation: 11

Joining two tables in SQL to get the SUM between two dates

I'm new to SQL and this website so apologies if anything is unclear.

Basically, I got two separate tables:

Table A:

CustomerID | PromoStart | PromoEnd
1          | 2020-05-01 | 2020-05-30
2          | 2020-06-01 | 2020-07-30
3          | 2020-07-01 | 2020-10-15

Table B:

CustomerID | Date       | Payment |
1          | 2020-02-15 | 5000    |
1          | 2020-05-04 | 200     |
1          | 2020-05-28 | 100     |
1          | 2020-06-05 | 1000    |
2          | 2020-06-10 | 20      |
2          | 2020-07-25 | 500     |
2          | 2020-08-02 | 1000    |
3          | 2020-09-05 | 580     |
3          | 2020-12-01 | 20      |

What I want is to get the sum of all payments that fall between PromoStart and PromoEnd for each customer.

so the desired result would be :

CustomerID  | TotalPayments
1           | 300
2           | 520
3           | 580

I guess this would involve an inner (left?) join and a where clause however I just can't figure it out.

Upvotes: 1

Views: 852

Answers (3)

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

I hope I didn't overlook something important but it seems to me simple join on range matching condition should be sufficient:

with a (CustomerID , PromoStart , PromoEnd) as (values
(1          , date '2020-05-01' , date '2020-05-30'),
(2          , date '2020-06-01' , date '2020-07-30'),
(3          , date '2020-07-01' , date '2020-10-15')
), b (CustomerID , d       , Payment ) as (values
(1          , date '2020-02-15' , 5000    ),
(1          , date '2020-05-04' , 200     ),
(1          , date '2020-05-28' , 100     ),
(1          , date '2020-06-05' , 1000    ),
(2          , date '2020-06-10' , 20      ),
(2          , date '2020-07-25' , 500     ),
(2          , date '2020-08-02' , 1000    ),
(3          , date '2020-09-05' , 580     ),
(3          , date '2020-12-01' , 20      )
)
select a.CustomerID, sum(b.Payment)
from a
join b on a.CustomerID = b.CustomerID and b.d between a.PromoStart and PromoEnd
group by a.CustomerID

Db fiddle here.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use a correlated subquery or join with aggregation. The correlated subquery looks like:

select a.*,
       (select sum(b.payment)
        from b
        where b.customerid = a.customerid and
              b.date >= a.promostart and
              b.date <= a.promoend
       ) as totalpayments
from a;

You don't mention your database, but this can take advantage of an index on b(customerid, date, payment). By avoiding the outer aggregation, this would often have better performance than an alternative using group by.

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656471

A LATERAL join would do it:

SELECT a.customer_id, b.total_payments
FROM   table_a a
LEFT   JOIN LATERAL (
   SELECT sum(payment) AS total_payments
   FROM   table_b
   WHERE  customer_id = a.customer_id
   AND    date BETWEEN a.promo_start AND a.promo_end
   ) b ON true;

This assumes inclusive lower and upper bounds, and that you want to include all rows from table_a, even without any payments in table_b.

Upvotes: 1

Related Questions