# Display sum of a column at the end of the table **URL:** \ **Category:** Ignition\ **Tags:** vision\ **Created:** [January 18, 2023, 9:46am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717 "2023-01-18T09:46:50Z")\ **Posts on this page:** 1\ **Showing post:** 6
**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\ **Post date:** [January 19, 2023, 3:32am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/6 "2023-01-19T03:32:11Z")
There are at least two approaches that would work here. You could write a second select statement that automatically generates the sums, and then append it to the last row of your existing query with a union. In this way, the data comes directly from the query with no intermediary scripting required. The query would look something like this: > **Sample Query** > > ```auto > SELECT > EquipmentName as 'Machine No', > ShiftID as 'Shift', > TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Idling' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Idling Time', > TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Jogging' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Jog Time', > TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Stopped' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Stop Time', > TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Not Connected' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'No Connection Time', > TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Rewinding' THEN RunTime ELSE 0 END) ),'%H:%i:%s')as 'Rewind Time' > FROM tbl_equipment > JOIN tbl_machinestatus ON tbl_machinestatus.EquipmentID = tbl_equipment.EquipmentID > WHERE tbl_machinestatus.ProcessArea = :iProcessArea and DATE(DateofProduction) = DATE( :iStartDate ) > AND IF (:iEquipmentID =100,tbl_machinestatus.EquipmentID ,:iEquipmentID) = tbl_machinestatus.EquipmentID > AND IF(:iShift = 'ALL' , tbl_machinestatus.ShiftID ,:iShift)=tbl_machinestatus.ShiftID > GROUP BY EquipmentName > ORDER BY EquipmentName > > UNION > > SELECT > '', > 'Total', > SEC_TO_TIME(SUM(TIME_TO_SEC('Idling Time')) ,'%H:%i:%s')as 'Idling Time', > SEC_TO_TIME(SUM(TIME_TO_SEC('Jog Time')) ,'%H:%i:%s') as 'Jog Time', > SEC_TO_TIME(SUM(TIME_TO_SEC('Stop Time')) ,'%H:%i:%s') as 'Stop Time', > SEC_TO_TIME(SUM(TIME_TO_SEC('No Connection Time')) ,'%H:%i:%s') as 'No Connection Time', > SEC_TO_TIME(SUM(TIME_TO_SEC('Rewind Time')) ,'%H:%i:%s') as 'Rewind Time' > FROM ( > SELECT > EquipmentName as 'Machine No', > ShiftID as 'Shift', > TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Idling' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Idling Time', > TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Jogging' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Jog Time', > TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Stopped' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'Stop Time', > TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Not Connected' THEN RunTime ELSE 0 END)),'%H:%i:%s') as 'No Connection Time', > TIME_FORMAT(SEC_TO_TIME( SUM(CASE WHEN StateChangeFrom = 'Rewinding' THEN RunTime ELSE 0 END) ),'%H:%i:%s')as 'Rewind Time' > FROM tbl_equipment > JOIN tbl_machinestatus ON tbl_machinestatus.EquipmentID = tbl_equipment.EquipmentID > WHERE tbl_machinestatus.ProcessArea = :iProcessArea and DATE(DateofProduction) = DATE( :iStartDate ) > AND IF (:iEquipmentID =100,tbl_machinestatus.EquipmentID ,:iEquipmentID) = tbl_machinestatus.EquipmentID > AND IF(:iShift = 'ALL' , tbl_machinestatus.ShiftID ,:iShift)=tbl_machinestatus.ShiftID > GROUP BY EquipmentName > ORDER BY EquipmentName > ) > > ``` It is probable that the first two columns will have to be cast as strings to prevent a datatype error, but otherwise, the above query should be pretty close to what you are needing. The other approach would be to modify the dataset after it has been pulled in by the button but before it has been applied to the table. Here is an example script that will accomplish this: > **Source Code** > > ```auto > from datetime import datetime, timedelta > def getSumValue(elapsed_time_strings):#calculates the sum value for each column > total_elapsed_time = timedelta() > for elapsed_time_string in elapsed_time_strings: > hours, minutes, seconds = [int(x) for x in elapsed_time_string.split(":")] > total_elapsed_time += timedelta(hours=hours, minutes=minutes, seconds=seconds) > total_elapsed_time_string = str(total_elapsed_time) > if total_elapsed_time_string.startswith('0:'): > total_elapsed_time_string = total_elapsed_time_string[2:] > return total_elapsed_time_string > > #creates the data for the getSumValue function > dataset = #INSERT NAMED QUERY HERE > sumRow = [None, 'Total'] > for column in range(2, dataset.columnCount): > elapsed_time_strings = [] > for row in range(dataset.rowCount): > elapsed_time_strings.append(dataset.getValueAt(row, column)) > sumRow.append(getSumValue(elapsed_time_strings)) > > #add the 'Total' row to the querried dataset and apply it to the table > event.source.parent.getComponent('Power Table').data = system.dataset.addRow(dataset, dataset.rowCount, sumRow) > > ``` Here is the script output as it appeared in my test table: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/9/29fcd1d332d4ac7511d949eaf61e268b84ebfe0f.png) --- _[View the full topic](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717)._