MYSQL to calculate YTD

Question

I am starting to learn MYSQL and have an issue at hand that I cannot figure out. My table looks like this:

OU Date Amt A20 201701 1000 A20 201705 1000 A20 201708 1000 B10 201702 1000 B10 201705 1000 What I would like is to calculate the YTD amount for each row by OU that looks like this:

OU Date Amt YTD A20 201701 1000 1000 A20 201705 1000 2000 A20 201708 1000 3000 B10 201702 1000 1000 B10 201705 1000 2000

I tried different sum and sumif functions but couldn't get the correct results. Can anyone help? Any input is much appreciated!

Answer 1

One approach uses a correlated subquery:

SELECT
    OU,
    Date,
    Amt,
    (SELECT SUM(t2.Amt) FROM yourTable t2
     WHERE t1.OU = t2.OU AND t2.Date <= t1.Date) AS YTD
FROM
    yourTable t1
ORDER BY
    OU, Date;

The trick the subquery uses is to sum, for each record in your table, the amounts of the current or earlier dates, from those records sharing the same OU value as the current record. You can explore the demo below to get a better understanding.

Demo here:

Rextester