CODEWITHSUNDEEP
sqlsqlite
Asnag
Asnag

Reputation: 90

subtract values of same column

I´m trying to subtract the values of a weight column order by date desc, so I can get the difference between them ,example:

weight values :90-93-94-97

desired output: 0-3-1-3

90-90 | 93-90 |94-93| 97-94

Upvotes: 0

Views: 851

Answers (2)

Jp Vinjamoori
Jp Vinjamoori

Reputation: 1271

SQL Lite does not support running total or cumulative totals. But you can compute it round about way

Assume the following tables: tb_user, tb_weight:

|------------------|    |----------------------|
|   Id   | Name    |    |UserId| Date  | Weight|
|------------------|    |----------------------|
|   U1   | John    |    |  U1  | 1-Jan | 90    |
|   U2   | Jill    |    |  U2  | 2-Jan | 93    |
|------------------|    |  U1  | 3-Jan | 94    |
                        |  U2  | 4-Jan | 97    |
                        |----------------------|


    SELECT user.name, 
           current.date, 
           current.weight, 
           current.weight - (SELECT previous.weight -- select weight on the previous day available
                               FROM tb_weight previous
                              WHERE previous.date < current.date
                                AND previous.userId = user.id
                           ORDER BY previous.date DESC
                              LIMIT 1) -- select the top 1 record
    FROM tb_weight current
  INNER JOIN tb_user users ON (users.id = current.UserId)
      ORDER BY current.date DESC

Update on 22SEP2014: Added join with User table as requested

Upvotes: 1

Bohemian
Bohemian

Reputation: 425198

Here's a query that joins to rows to the previous row (except for the first weight row, which subtracts itself)

select x.date, x.weight, x.weight - coalesce(y.weight, x.weight) delta
from (
  select w.date, w.weight, max(d.date) previous
  from weights w
  left join weights d on d.date < w.date
  group by w.date, w.weight) x
left join weights y on y.date = previous
order by x.date

See SQLFiddle using data:

2014-01-01 90
2014-01-02 93
2014-01-03 94
2014-01-04 94
2014-01-05 93
2014-01-06 97

and producing output of:

date        weight  delta
2014-01-01  90      0
2014-01-02  93      3
2014-01-03  94      1
2014-01-04  94      0
2014-01-05  93      -1
2014-01-06  97      4

Upvotes: 1

Related Questions