# Trying to query difference between rows in table

**URL:** <https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991>\
**Category:** Ignition\
**Tags:** ignition78, vision\
**Created:** [October 31, 2024, 11:19am UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991 "2024-10-31T11:19:35Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [October 31, 2024, 11:19am UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991/1 "2024-10-31T11:19:35Z")

</div>

Long story short in an old 7.8.5 system.

I have a table where I query to populate. I want to get a difference between each row.

Lets say

row 1  
row 2

i could have a column that = row 2 - row 1 is x

Here is my current code minus that additional col.

```python
SELECT wastewaterlog.t_stamp AS Date_And_Time,
  wastewaterlog.GrandTotal AS Totalizer_Read_Gallons,
  wastewaterlog.FlowRate / 10 AS Flow_Rate_GPM
FROM wastewaterlog
WHERE wastewaterlog.t_stamp BETWEEN "{Root Container.Start Date.text} 00:00:00" AND "{Root Container.End Date.text} 23:59:59"

```

i basically want to check totalizer read gallos and indicate how many gallons were added each row.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [October 31, 2024, 12:09pm UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991/2 "2024-10-31T12:09:39Z")

</div>

This is a job for window functions, like `lead` and `lag`. What db flavor are you using?

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [October 31, 2024, 12:14pm UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991/3 "2024-10-31T12:14:16Z")

</div>

Tried lag and got an error. using MySQL 5.1

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [October 31, 2024, 12:36pm UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991/5 "2024-10-31T12:36:49Z")

</div>

Got this code to work. But it takes a little bit to load. Any idea how I can speed it up?

```python
SELECT 
    main.t_stamp AS Date_And_Time,  
    main.GrandTotal AS Totalizer_Read_Gallons,  
    main.FlowRate / 10 AS Flow_Rate_GPM,
    main.GrandTotal - IFNULL((SELECT sub.GrandTotal 
                             FROM wastewaterlog sub 
                             WHERE sub.t_stamp < main.t_stamp 
                             ORDER BY sub.t_stamp DESC 
                             LIMIT 1), main.GrandTotal) AS Gallons_Change
FROM wastewaterlog main
WHERE main.t_stamp BETWEEN "{Root Container.Start Date.text} 00:00:00" AND "{Root Container.End Date.text} 23:59:59"
ORDER BY main.t_stamp;

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [October 31, 2024, 12:47pm UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991/6 "2024-10-31T12:47:39Z")

</div>

```python
SELECT 
	t_stamp AS Date_And_Time,
	GrandTotal AS Totalizer_Read_Gallons,
	GrandTotal - LAG(GrandTotal) OVER (ORDER BY t_stamp) AS diff,
	FlowRate / 10 AS Flow_Rate_GPM
FROM 
	wastewaterlog
WHERE
...

```

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [October 31, 2024, 12:58pm UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991/7 "2024-10-31T12:58:08Z")

</div>

missing the change gallons in that edit. Just want to verify do I insert that?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [October 31, 2024, 1:03pm UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991/8 "2024-10-31T13:03:23Z")

</div>

I named it `diff` on a whim, so you can just change it to `Gallons_Change`

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [October 31, 2024, 1:10pm UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991/9 "2024-10-31T13:10:08Z")

</div>

Gave me an error, gotta hit they hay, i'll upload it in a little.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [October 31, 2024, 1:32pm UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991/10 "2024-10-31T13:32:34Z")

</div>

Looks like the window functions in MySQL weren't added until version 8.0. Can you upgrade it?

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [October 31, 2024, 1:48pm UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991/11 "2024-10-31T13:48:40Z")

</div>

Negative.

---

<div class="post-metadata">

**Author:** ![Roderick\_Douglass](https://avatars.discourse-cdn.com/v4/letter/r/f17d59/32.png) [@Roderick\_Douglass](https://forum.inductiveautomation.com/u/Roderick_Douglass)\
**Post date:** [November 7, 2024, 6:59am UTC](https://forum.inductiveautomation.com/t/trying-to-query-difference-between-rows-in-table/97991/12 "2024-11-07T06:59:42Z")

</div>

I'd probably create a Stored Procedure in the DB and just call it from Ignition.

[SQL Stored Procedures | Ignition User Manual](https://www.docs.inductiveautomation.com/docs/8.1/platform/sql-in-ignition/writing-sql-queries/sql-stored-procedures#:~:text=The%20recommended%20approach%20to%20calling%20a%20Stored%20Procedure,2%20Using%20system.db.execSProcCall%20to%20execute%20the%20Stored%20Procedure.)