3

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?

0

1 Answer 1

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.