# Ignition SQLite window functions & adding a running total column **URL:** \ **Category:** Ignition\ **Tags:** database, ignition80\ **Created:** [March 25, 2021, 6:39pm UTC](https://forum.inductiveautomation.com/t/ignition-sqlite-window-functions-adding-a-running-total-column/44919 "2021-03-25T18:39:32Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\ **Post date:** [March 25, 2021, 6:39pm UTC](https://forum.inductiveautomation.com/t/ignition-sqlite-window-functions-adding-a-running-total-column/44919/1 "2021-03-25T18:39:33Z")
Ignition 8.1.2 appears to be using SQLite 3.23.1: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/a/ca2aa5e0c2a9424f0fb7cac84e7231f0849ef08a.png) [Window functions were added 2.5 years ago in version 3.25.0](https://sqlite.org/changes.html). Is there a way to get access to these in Ignition? Perhaps another SQLite driver? Alternatively, how would one write a query to return a column plus a running total column without window functions? What I’m looking for is a way to write a query to get records whose running total is between two values when running total is calculated ordered by date. I’m just getting familiar with SQL, so feel free to point out the obvious I may be missing! This is on a table with \>20k rows. Joining the table to itself times out. At this point I’m leaning towards retrieving the whole set and processing rows in a script transform to get what we need, though it seems like there should be a better way. ---
**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\ **Post date:** [March 26, 2021, 4:09pm UTC](https://forum.inductiveautomation.com/t/ignition-sqlite-window-functions-adding-a-running-total-column/44919/2 "2021-03-26T16:09:30Z")
It turns out calculating a running total and selecting records within the desired running total window by scripting is quite responsive in Perspective even though it has to process 20k rows (usually selecting a window near the end). I’m guessing it was slow in the script console due to downloading the whole result set a slow connection before it could process it. That all said, I think we’ll move this to a DB that supports windows. I am still curious if there’s a way to get a newer version of SQLite in Ignition as it seems like a good solution for smaller amounts of data that is mostly static. I wouldn’t want to use it for anything that needs window functions unless we can get a current version that supports them. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [March 26, 2021, 5:36pm UTC](https://forum.inductiveautomation.com/t/ignition-sqlite-window-functions-adding-a-running-total-column/44919/3 "2021-03-26T17:36:51Z")
> [@witman](#): > > I am still curious if there’s a way to get a newer version of SQLite in Ignition as it seems like a good solution for smaller amounts of data that is mostly static. Technically, you might be able to drop in a new sqlite JDBC driver (SQLite JDBC drivers are usually just pretty thin shims that call native SQLite code), but in practice that's probably not a good idea, if it works at all. We had some issues with ARM when we last tried upgrading our bundled SQLite driver, but we can take another look and see if we can bump the version across all platforms. ---
**Author:** ![witman](https://avatars.discourse-cdn.com/v4/letter/w/d2c977/32.png) [@witman](https://forum.inductiveautomation.com/u/witman)\ **Post date:** [October 11, 2022, 8:04pm UTC](https://forum.inductiveautomation.com/t/ignition-sqlite-window-functions-adding-a-running-total-column/44919/4 "2022-10-11T20:04:42Z")
Update: SQLite driver upgrade has arrived. > [@Nightly 8.1 Changelogs - 2022](https://forum.inductiveautomation.com/t/nightly-8-1-changelogs-2022/54636/147): > > Weekly Changelog: 8.1.22-b20221010 Platform 6110: Attempts to remove old scheduled backups blocked by macOS sandboxing restrictions, results in NPE Added warnings to the Diagnostics Logs when the deletion of old backups (as part of Scheduled Backups) is prevented by Os restrictions. 2163: Upgrade SQLite JDBC driver Upgrade embedded SQLite and its JDBC driver to 3.39.2.0. Visualization 4236: Color Picker has clipping issues when alpha \< 255 Fixed issue with the clipping region improperly se…