Generate linear growth report

Question

My table has the following data:

2016-01-01 : 1
2016-01-02 : 0
2016-01-03 : 4
2016-01-04 : 1
2016-01-05 : 2

I want to generate a report (linear growth) like this:

date       : linear growth
2016-01-01 : 1
2016-01-02 : 1
2016-01-03 : 5
2016-01-04 : 6
2016-01-05 : 8

Are there any good ideas for a SQL statement to do this?

Answer 1

MySQL doesn't support Window Functions such as SUM(...) OVER(...) but it can be calculated using a variable, a self join or a subquery.

Queries using the question's sample

Variable

SELECT created, value
    , @growth := @growth + value as growth
FROM data
  , (SELECT @growth := 0 as growth) as v
ORDER BY created

Output

| created                   | value | growth |
|---------------------------|-------|--------|
| January, 01 2016 00:00:00 |     1 |      1 |
| January, 02 2016 00:00:00 |     0 |      1 |
| January, 03 2016 00:00:00 |     4 |      5 |
| January, 04 2016 00:00:00 |     1 |      6 |
| January, 05 2016 00:00:00 |     2 |      8 |

Self JOIN

This can also be done with a self JOIN:

SELECT d1.created, d1.value
    , SUM(d2.value) as growth
FROM data d1
INNER JOIN data d2
    ON d1.created >= d2.created
GROUP BY d1.created, d1.value
ORDER BY d1.created;

Subquery

Or with a subquery:

SELECT d1.created, d1.value
    , (
      SELECT SUM(value) 
      FROM data
      WHERE created <= d1.created
    ) as growth
FROM mydata d1
ORDER BY d1.created;

• This SQL Fiddle contains both queries (variable, self join and subquery)

---

Query with growth for several IDs

Variables

If you want to calculate the growth for several IDs, an extra @id variable and a CASE statement can be added:

SELECT created, value
    , CASE WHEN @id <> d.id THEN @growth := value 
        ELSE @growth := @growth + value
      END as growth
    , @id := d.id as id
FROM data d
  , (SELECT @growth := 0 as growth, @id := 0 as id) as v
ORDER BY d.id, d.created;

Self JOIN

SELECT d1.created, d1.value
    , SUM(d2.value) as growth
    , d1.id
FROM data d1
INNER JOIN data d2
    ON d1.created >= d2.created AND d1.id = d2.id
GROUP BY d1.created, d1.value, d1.id
ORDER BY d1.created;

Subquery

SELECT d1.id, d1.created, d1.value
    , (
      SELECT SUM(value) 
      FROM data
      WHERE created <= d1.created AND id = d1.id
    ) as growth
FROM data d1
ORDER BY d1.id, d1.created;

• This SQL Fiddle contains both queries for growth by IDs (variables, self join or subquery)