CODEWITHSUNDEEP
databasesqlite
core
core

Reputation: 65

Programming In SQLITE

In college I learned PL/SQL, which I used to insert/update data into table programmatically.

So is there any way to do it in SQLITE?

I have one table book which has two columns: readPages and currentPage. readPage contains info about how many pages I've read today and currentPage shows total read pages till today.

Currently I have data for only readPages so I want to calculate currentPage for past days, e.g.

readPages:   19  10  43  20  35   # I have data for 5 days
currentPage: 19  29  72  92  127  # I want to calculate it

So this can be easy with programming, but how to do with sqlite as it is not like plsql.

Table Schema And data

Upvotes: 0

Views: 342

Answers (2)

Shawn
Shawn

Reputation: 52344

If you're using sqlite 3.25 or newer, something like:

SELECT date, readPages
      , sum(readPages) OVER (ORDER BY date) AS total_pages_read
FROM yourTableName
ORDER BY date;

will compute the running total of pages.

Upvotes: 1

forpas
forpas

Reputation: 164089

The order of the rows can be determined by id or by date.
The problem with the column date is that its format: 'DD-MM' is not comparable.
Better change it to something like: 'YYYY-MM-DD'.
Since your version of SQLite does not allow you to use window functions, you can do what you need with this:

update findYourWhy 
set currentPage = coalesce(
  (select sum(f.readPage) from findYourWhy f where f.id <= findYourWhy.id), 
  0
);

If you change the format of the date column, you can also do it with this:

update findYourWhy 
set currentPage = coalesce(
  (select sum(f.readPage) from findYourWhy f where f.date <= findYourWhy.date), 
  0
);

See the demo.

CREATE TABLE findYourWhy (
    id  INTEGER,
    date    TEXT,
    currentPage INTEGER,
    readPage    INTEGER,
    PRIMARY KEY(id)
);
INSERT INTO findYourWhy (id,date,currentPage,readPage) VALUES 
 (1,'06-05',null,36),
 (2,'07-05',null,9),
 (3,'08-05',null,12),
 (4,'09-05',null,5),
 (5,'10-05',null,12),
 (6,'11-05',null,13),
 (7,'12-05',null,2),
 (8,'13-05',null,12),
 (9,'14-05',null,3),
 (10,'15-05',null,5),
 (11,'16-05',null,6),
 (12,'17-05',null,7),
 (13,'18-05',null,7);

Results:

| id  | date  | currentPage | readPage |
| --- | ----- | ----------- | -------- |
| 1   | 06-05 | 36          | 36       |
| 2   | 07-05 | 45          | 9        |
| 3   | 08-05 | 57          | 12       |
| 4   | 09-05 | 62          | 5        |
| 5   | 10-05 | 74          | 12       |
| 6   | 11-05 | 87          | 13       |
| 7   | 12-05 | 89          | 2        |
| 8   | 13-05 | 101         | 12       |
| 9   | 14-05 | 104         | 3        |
| 10  | 15-05 | 109         | 5        |
| 11  | 16-05 | 115         | 6        |
| 12  | 17-05 | 122         | 7        |
| 13  | 18-05 | 129         | 7        |

Upvotes: 1

Related Questions