SQL subtraction?

Morning all.

I’m still new to Ignition and very new to SQL, I’ve hit a bit of a wall with an SQL command.

I have a 15 minute transaction group running, in this group I store various OPC values including a accumulated KWh value which will always get bigger. What I’d like to do is to take the second to last value and subtract it from the latest value to give a KWh in the last 15 minutes value, then place this value in a dedicated column in the same table and row of the latest value.

I know how to find the last value (using SELECT COUNT) and I so I can find the second to last value as well. I have tried using an expression tag to read the current value minus the latest stored value and this works well, and then have the transaction group reads the tag. At this point the value seem to go wrong and read too high by almost double, if as if its reading a 30 min value but not exactly.
Is there a SQL request that could do this without the need for an expression tag?


This type of query can be a bit tricky sometimes. I don’t know which database you are using, but the example below shows how to get the difference of a column from the last two rows:SELECT t1.kwh - (SELECT t2.kwh FROM history t2 WHERE t2.kwh_ndx < t1.kwh_ndx ORDER BY t2.kwh_ndx DESC LIMIT 1) FROM history t1 ORDER BY t1.kwh_ndx DESC LIMIT 1If you are using Oracle there are built-in functions for this.

With that said, it is probably better to store the difference in the table using the transaction group. You can create some expressions to do this:

  1. The first expression item called “lastkwh” grabs the last kwh from a variable and if it is the first time bring back a default value like 0:getVariable("lastkwh", 0)2) The next expression item does the subtraction using the latest tag value and the previous expression item:{[__GROUP__]LiveKWHTag} - {[__GROUP__]lastkwh}Store that expresssion to a column in the database.
  2. The last expression will update the lastkwh variable to the new tag:storeVariable("lastkwh", {[__GROUP__]LiveKWHTag})That is it! You should get the difference in your table.

Thanks Travis

I could not get the query to work in Mysql but with a llitle tinkering I came up with

SELECT Cardiff_KWh -(SELECT Cardiff_KWh FROM 15min_kwh where 15min_Kwh_ndx = (SELECT COUNT() -1 FROM 15min_Kwh))FROM 15min_kwh where 15min_Kwh_ndx = (SELECT COUNT() FROM 15min_Kwh)

I put this as a SQL triggered expression and it works a treat, all I have to do is replace the Cardiff name for each site, this will be useful for all my accumulators

Perhaps I can thank you in person in February.


You could also do -

SELECT t1.Cardiff_KWh - t2.Cardiff_KWh FROM 15min_Kwh t1, 15min_Kwh t2 WHERE (t1.15min_Kwh_ndx-1) = t2.15min_Kwh_ndx ORDER BY t1.15min_Kwh_ndx DESC LIMIT 1It joins the table into itself. t1 is the alias for the table, t2 is the alias for the table with all the records offset by one row. You could change the t1 to “current” and t2 to “previous” for readability.

The statement works like this - the ORDER BY clause says give me the last row of the table (the row that contains your last value t1.Cardiff_KWh), the WHERE clause says give me the row of the table where the index is one less than the current index (the row that contains your previous value t2.Cardiff_KWh), the SELECT line subtracts those two values.

It should work for any database and you might find it easier to understand if you have to come back to it in the future.

Many thanks Pat

SQl is new to me and I was unaware of the meaning of t1. t2 etc, over the weekend I’ve done a little work and with your explanation I can now see how this works.

What are the benefits of making the query this way rather than the way I found?


[quote=“Powerplan”]What are the benefits of making the query this way rather than the way I found?

  1. I’d say easier to understand what the query I posted is doing, there’s only one SELECT statement to figure out; to figure out what your query is doing I have to figure out (4) SELECT statements.

  2. It probably executes faster, only one SELECT statement.

  3. SELECT COUNT() FROM 15min_Kwh isn’t a good way to get the last row of the table, ORDER BY 15min_kwh_ndx DESC LIMIT 1 is better. If just one row gets deleted from the table your 15min_kwh_ndx value and the number of rows in the table will be off, SELECT COUNT() FROM 15min_Kwh won’t give you the last row anymore. If you wanted to stick with your query I would suggest using SELECT MAX(15min_kwh_ndx) FROM 15min_Kwh instead.

Thanks again Pat, what you say makes sense.

I will get myself on a SQL course in the new year, we are also advertising for a Java/SQL/python person to join the team so perhaps that will help.