# Data cells

**URL:** <https://forum.inductiveautomation.com/t/data-cells/10813>\
**Category:** Ignition\
**Created:** [November 21, 2015, 1:27pm UTC](https://forum.inductiveautomation.com/t/data-cells/10813 "2015-11-21T13:27:30Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Elettricman](https://avatars.discourse-cdn.com/v4/letter/e/e5b9ba/32.png) [@Elettricman](https://forum.inductiveautomation.com/u/Elettricman)\
**Post date:** [November 21, 2015, 1:27pm UTC](https://forum.inductiveautomation.com/t/data-cells/10813/1 "2015-11-21T13:27:30Z")

</div>

Hello, excuse my language but use a translator 😃 , I ask you one help, I have tables that total the kwh, I need to extract the daily consumption directly from the ignition,  
you think you can. ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:")  
Currently I take the values of two days, and I xy subtract the daily.  
But the analysis should be done every three months  
I attach a screen shot

Thank you very much

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/9/945d8474faafec2497c4d899cede660d0989ab4b.png)

---

<div class="post-metadata">

**Author:** ![Elettricman](https://avatars.discourse-cdn.com/v4/letter/e/e5b9ba/32.png) [@Elettricman](https://forum.inductiveautomation.com/u/Elettricman)\
**Post date:** [November 23, 2015, 5:52pm UTC](https://forum.inductiveautomation.com/t/data-cells/10813/2 "2015-11-23T17:52:08Z")

</div>

Hello, I explain, I have a table with values in kwh per day, every day I have to know the difference between consumption before and after and put the new value in the same table in another column and then extract all in csv,

Thank you very much

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 23, 2015, 6:53pm UTC](https://forum.inductiveautomation.com/t/data-cells/10813/3 "2015-11-23T18:53:44Z")

</div>

This is really a SQL problem. I would use a window aggregate function like so:`SELECT t_stamp,
  "kWh",
  "kWh" - lag("kWh") OVER (ORDER BY t_stamp) AS "daily_kWh"
FROM mytable
WHERE t_stamp > .....
ORDER BY t_stamp`PostgreSQL [documentation](http://www.postgresql.org/docs/current/interactive/queries-table-expressions.html#QUERIES-WINDOW). Syntax varies a bit among the various DB platforms. MySQL and MariaDB can’t do this at all.

---

<div class="post-metadata">

**Author:** ![Elettricman](https://avatars.discourse-cdn.com/v4/letter/e/e5b9ba/32.png) [@Elettricman](https://forum.inductiveautomation.com/u/Elettricman)\
**Post date:** [November 23, 2015, 7:33pm UTC](https://forum.inductiveautomation.com/t/data-cells/10813/4 "2015-11-23T19:33:52Z")

</div>

Hello , thank you for the swift response , I put the code in SQL syntax error , I feel the network administrator for this problem , I would like to know if this is fair that I put .  
Thank you very much

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/27da57c805ae976bad1cc9c04e2967f2fc25066d.png)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 23, 2015, 8:16pm UTC](https://forum.inductiveautomation.com/t/data-cells/10813/5 "2015-11-23T20:16:38Z")

</div>

The error message shows you are using MySQL. It doesn’t support window aggregate functions, as I stated.  
You pasted my code sample verbatim. I made up column names. I didn’t provide a complete WHERE clause. You have to tailor the SQL to suit your application.  
Also, please trim your screenshots. All that useless whitespace makes the whole topic really wide and hard to read. For error messages, it is better to switch the error message to “full”, copy the text, and paste it inside code /code tags.

---

<div class="post-metadata">

**Author:** ![Elettricman](https://avatars.discourse-cdn.com/v4/letter/e/e5b9ba/32.png) [@Elettricman](https://forum.inductiveautomation.com/u/Elettricman)\
**Post date:** [November 23, 2015, 9:00pm UTC](https://forum.inductiveautomation.com/t/data-cells/10813/6 "2015-11-23T21:00:24Z")

</div>

Ok, sorry ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:") , next time I paste only the error, I have to understand if they are on the right track

SELECT t\_stamp,  
“kwh\_raw”,  
“kwh\_raw” - lag(“kwh\_raw”) OVER (ORDER BY t\_stamp) AS “kwh\_giorno”  
FROM Table 1  
WHERE t\_stamp \>= ‘{Root Container.Calendar.date}’ AND  
t\_stamp \<= ‘{Root Container.Calendar.latchedDate}’  
ORDER BY t\_stamp

thank you

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 23, 2015, 9:17pm UTC](https://forum.inductiveautomation.com/t/data-cells/10813/7 "2015-11-23T21:17:37Z")

</div>

[quote=“Elettricman”]`SELECT t_stamp,
  "kwh_raw",
  "kwh_raw" - lag("kwh_raw") OVER (ORDER BY t_stamp) AS "kwh_giorno"
FROM Table 1
WHERE t_stamp >= '{Root Container.Calendar.date}' AND
      t_stamp <= '{Root Container.Calendar.latchedDate}'
ORDER BY t_stamp`[/quote]This would work in PostgreSQL since v8.4, in MS SQL Server since 2012, and Oracle. (I don’t know how far back it is supported in Oracle, but certainly before 10g.)