# Display sum of a column at the end of the table

**URL:** <https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717>\
**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:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\
**Post date:** [January 18, 2023, 9:46am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/1 "2023-01-18T09:46:50Z")

</div>

Hi,  
I would like to display status of machines in vision like below,  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/b/cb9149a38cb2f53a3fbb74886a537332ca3a0d0a.png)  
Is there any option to display the sum of each column at the end of the table as shown in the pic.

---

<div class="post-metadata">

**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 18, 2023, 9:49am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/2 "2023-01-18T09:49:53Z")

</div>

How are you populating the table?

---

<div class="post-metadata">

**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\
**Post date:** [January 18, 2023, 9:50am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/3 "2023-01-18T09:50:26Z")

</div>

by passing the named query via button click.

---

<div class="post-metadata">

**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 18, 2023, 9:53am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/4 "2023-01-18T09:53:11Z")

</div>

What is the datatype of the time columns?

---

<div class="post-metadata">

**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\
**Post date:** [January 18, 2023, 10:04am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/5 "2023-01-18T10:04:04Z")

</div>

int.  
The query i used:

```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

```

type or paste code here

```auto

```

---

<div class="post-metadata">

**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")

</div>

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)

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 19, 2023, 10:36am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/7 "2023-01-19T10:36:11Z")

</div>

> [@justinedwards.jle](#):
>
> ```auto
> elapsed_time_strings = []
> for row in range(dataset.rowCount):
> elapsed_time_strings.append(dataset.getValueAt(row, column))
> 
> ```

`dataset.getColumnAsList()` will do that for you

```python
sumRow = [None, 'Total'] + [getSumValue(ds.getColumnAsList(col) for col in range(2, ds.columnCount)]

```

---

<div class="post-metadata">

**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, 10:40am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/8 "2023-01-19T10:40:40Z")

</div>

Thanks. As I've said before, I'm a huge fan of your refactors and optimizations.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 19, 2023, 10:43am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/9 "2023-01-19T10:43:39Z")

</div>

Thanks, but in this case... It's an IA function, I can't take the credit ;p

I could brew something with maps and itertools if you want, but I doubt it would be an optimization !  
Could be fun though.

---

<div class="post-metadata">

**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, 10:45am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/10 "2023-01-19T10:45:34Z")

</div>

The other day, I was really hoping you would jump in on this one

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 19, 2023, 10:52am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/11 "2023-01-19T10:52:59Z")

</div>

Holy %^$!@ that's a whole lot of ifs !

---

<div class="post-metadata">

**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, 10:55am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/12 "2023-01-19T10:55:09Z")

</div>

That needs a serious refactor. A thousand lines could probably be eliminated from that easily. The whole time I was working on that, I was ~~wondering~~ hoping: is Pascal seeing this? I knew if anybody could fix it, you could.

---

<div class="post-metadata">

**Author:** ![Dharani\_T](https://avatars.discourse-cdn.com/v4/letter/d/bc8723/32.png) [@Dharani\_T](https://forum.inductiveautomation.com/u/Dharani_T)\
**Post date:** [January 30, 2023, 5:05am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/13 "2023-01-30T05:05:48Z")

</div>

Incase, is there any possibility to display the background of the last row(Total of each column) with different colour?

---

<div class="post-metadata">

**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 30, 2023, 6:44am UTC](https://forum.inductiveautomation.com/t/display-sum-of-a-column-at-the-end-of-the-table/68717/14 "2023-01-30T06:44:03Z")

</div>

If you are using a power table, you can add the following script to your configureCell extension function:

```auto
#def configureCell([...]):
	if selected:
		return {'background': self.selectionBackground}
	elif rowIndex == self.data.rowCount - 1:
		return {'background': '#DDDDDD'}

```

**Result:**

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/9/89a34090fa46409934b37b1a9a6e811d7b850817.png)