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

Ignition
,
1

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:'

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?

2

How about something like this..

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.