# Summing a select statement result

**URL:** <https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341>\
**Category:** Archived Topics\
**Created:** [September 26, 2011, 8:57pm UTC](https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341 "2011-09-26T20:57:36Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [September 26, 2011, 8:57pm UTC](https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341/1 "2011-09-26T20:57:36Z")

</div>

Maybe someone can help me out with this because it’s driving me crazy. I have two tables that I am trying to get a grand total type sum from.

This query gives me a list of hours that I would like to sum up:

`SELECT count(Cutfloor_Accusort_Product_Counts."Product Number") * Cutfloor_EH.uninflated as hours
FROM Cutfloor_Accusort_Product_Counts
inner join Cutfloor_EH
     on cast(Cutfloor_EH.product_number as varchar) = "Product Number"
where t_stamp >= '{Root Container.Date Range.startDate}'
     and t_stamp <= '{Root Container.Date Range.endDate}'
group by Cutfloor_EH.uninflated`

I get this, which is the correct information:

![](https://us1.discourse-cdn.com/inductiveautomation/original/1X/1b430b2794597d008de89aab1197073b357bb84f.png)

The problem is that whenever I try and sum the result of this query I get errors, no matter what I do. Do I have to put this result into another table first or can I do this with one SQL query? Can I sum this up in Ignition by referencing this table somehow? I would like to display the result in a label component. Any help would be greatly appreciated!

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 26, 2011, 10:59pm UTC](https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341/2 "2011-09-26T22:59:51Z")

</div>

Taking out the group by clause would certainly give you the results you want.

But if you run the query in a script, you can run your sum on the result dataset. That would only query the table once.

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [September 26, 2011, 11:13pm UTC](https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341/3 "2011-09-26T23:13:25Z")

</div>

Hmmm… I tried taking the group by clause out already and MS SQL gave an error that Cutfloor\_EH.uninflated can’t be in the query unless part of a group by statement so I’m a little confused about that. I can try running it in a script and see if I get anywhere that way.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 26, 2011, 11:27pm UTC](https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341/4 "2011-09-26T23:27:20Z")

</div>

Hmm… okay… go figure!

The script would look something like this…

[code]query= “SELECT count(Cutfloor\_Accusort\_Product\_Counts.“Product Number”) \* Cutfloor\_EH.uninflated as hours FROM Cutfloor\_Accusort\_Product\_Counts inner join Cutfloor\_EH on cast(Cutfloor\_EH.product\_number as varchar) = “Product Number” where t\_stamp \>= ‘%s’ and t\_stamp \<= ‘%s’ group by Cutfloor\_EH.uninflated” %({Root Container.Date Range.startDate}, {Root Container.Date Range.endDate})

result=system.db.runUpdateQuery(query)

total=0  
headers=[“hours”]  
rows=[]  
for row in result:  
total += row[0]  
rows.append(row[0])  
data = system.dataset.toDataSet(headers, rows)

table = event.source.parent.getComponent(“Table”)  
table.data = data[/code]

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [September 27, 2011, 11:21am UTC](https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341/5 "2011-09-27T11:21:16Z")

</div>

Turns out that I already had that information in a table on the same page that I could use (duh… 😊) so I took your script and put it on a data change event and modified it to do what I wanted:

[code]if event.propertyName == “data”:  
result = event.source.data

```
total = 0.0
for row in range(result.rowCount):
	total = total + float(result.getValueAt(row,"Earned Hours"))
event.source.parent.getComponent('Total Hours').text = str(total)
[/code]

```

Thanks for the point in the right direction, much appreciated!

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 27, 2011, 1:42pm UTC](https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341/6 "2011-09-27T13:42:44Z")

</div>

Glad to help! 😃

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [September 28, 2011, 4:29pm UTC](https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341/7 "2011-09-28T16:29:45Z")

</div>

No scripts needed! Bind the label to an expression binding like:

```auto
sum({Root Container.Table.data}, "Earned Hours")
```

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [September 28, 2011, 11:29pm UTC](https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341/8 "2011-09-28T23:29:45Z")

</div>

Awww… are you kidding me? That’s way too simple 😠

Carl, you are a god among men ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:")

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [September 29, 2011, 4:20pm UTC](https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341/9 "2011-09-29T16:20:44Z")

</div>

Heh, you’re too kind.

Expressions are pretty handy - take a minute and explore the user manual’s [expression appendix](http://www.inductiveautomation.com/support/usermanuals/ignition/index.html?gen_aggregates_groupconcat.htm) if you haven’t already - lots of powerful things in there.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 29, 2011, 5:54pm UTC](https://forum.inductiveautomation.com/t/summing-a-select-statement-result/3341/10 "2011-09-29T17:54:53Z")

</div>

[quote=“Carl.Gould”]No scripts needed! Bind the label to an expression binding like:

```auto
sum({Root Container.Table.data}, "Earned Hours")
```

Well, sure, if you wanted to go that way… 😆