# Need help averaging out some data in a large table using queries

**URL:** <https://forum.inductiveautomation.com/t/need-help-averaging-out-some-data-in-a-large-table-using-queries/65299>\
**Category:** Ignition\
**Tags:** namedqueries, vision\
**Created:** [October 17, 2022, 6:18pm UTC](https://forum.inductiveautomation.com/t/need-help-averaging-out-some-data-in-a-large-table-using-queries/65299 "2022-10-17T18:18:13Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Brennan\_Joles](https://avatars.discourse-cdn.com/v4/letter/b/d2c977/32.png) [@Brennan\_Joles](https://forum.inductiveautomation.com/u/Brennan_Joles)\
**Post date:** [October 17, 2022, 6:18pm UTC](https://forum.inductiveautomation.com/t/need-help-averaging-out-some-data-in-a-large-table-using-queries/65299/1 "2022-10-17T18:18:13Z")

</div>

I have an SQL table that takes a ton of data from 30 different machines such as how long something should take to charge and how long it actually took. Here is my current code:'

```auto
select top 500
      schedTimeActual10,
      schedTimePlanned10,
      ChargeTrendBit6,
      tableNumber10,
      ChargeTrendBit11,
      ChargeTrendBit12,
      schedTimeActual3,
      schedTimePlanned3,
      ChargeTrendBit15,
      schedTimeActual6,
      schedTimePlanned4,
      tableNumber4,
      schedTimeActual9,
      schedTimePlanned9,
      tableNumber9,
      schedOverTimemins1,
      ChargeTrendBit7,
      ChargeTrendBit9,
      ChargeTrendBit13,
      programIndex6,
      schedOverTimemins6,
      schedTimeActual11,
      schedTimeActual7,
      schedTimePlanned11,
      schedTimePlanned6,
      tableNumber11,
      tableNumber6,
      tableNumber5,
      ChargeTrendBit8,
      ChargeTrendBit10,
      ChargeTrendBit14,
      programIndex7,
      schedOverTimemins7,
      schedOverTimemins5,
      schedTimeActual12,
      schedTimeActual8,
      schedTimePlanned12,
      schedTimePlanned7,
      tableNumber12,
      tableNumber7,
      ChargeTrendBit1,
      programIndex8,
      programIndex3,
      programIndex4,
      programIndex5,
      schedOverTimemins8,
      schedOverTimemins3,
      schedOverTimemins4,
      schedTimeActual13,
      schedTimeActual4,
      schedTimeActual5,
      schedTimePlanned13,
      schedTimePlanned8,
      schedTimePlanned5,
      tableNumber13,
      tableNumber3,
      tableNumber8,
      programIndex9,
      programIndex10,
      programIndex11,
      programIndex12,
      schedOverTimemins9,
      schedOverTimemins10,
      schedOverTimemins11,
      schedOverTimemins12,
      schedTimeActual14,
      schedTimeActual15,
      schedTimePlanned14,
      schedTimePlanned15,
      tableNumber14,
      tableNumber15,
      programIndex13,
      programIndex14,
      programIndex15,
      programIndex1,
      programIndex2,
      schedOverTimemins13,
      schedOverTimemins14,
      schedOverTimemins15,
      schedOverTimemins2,
      schedTimeActual1,
      schedTimeActual2,
      schedTimePlanned1,
      schedTimePlanned2,
      tableNumber1,
      tableNumber2,
      ChargeTrendBit2,
      ChargeTrendBit3,
      ChargeTrendBit4,
      ChargeTrendBit5,
      TableNumEast,
      t_stamp
from Tables_Data_East
where t_stamp BETWEEN (:StartDate) AND (:EndDate)
order by t_stamp desc

```

This is triggered anytime the TableNumEast changes. I would like to create another query that averages how long it takes to charge past the expected (tag is schedOverTimemins) by tableNumber and then also by the string of ChargeTrendBit. Any ideas the best way to go about this in Named Queries?

---

<div class="post-metadata">

**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\
**Post date:** [October 17, 2022, 7:00pm UTC](https://forum.inductiveautomation.com/t/need-help-averaging-out-some-data-in-a-large-table-using-queries/65299/2 "2022-10-17T19:00:07Z")

</div>

How about something like this..

```auto
select AVG(REGEXP_REPLACE(Columnname, '[^0-9]+', '')) AS 'NewValue'
From Tables_Data_East
WHERE Columnname LIKE 'schedOverTimemins%' AND where t_stamp BETWEEN (:StartDate) AND (:EndDate)

```

Edited to say this is for MYSQL 8+, not sure if it works on other SQL flavours.