Summing a select statement result

Archived Topics
1

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:


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!

2

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.

3

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.

4

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]

5

Turns out that I already had that information in a table on the same page that I could use (duh… :blush:) 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!

6

Glad to help! :smiley:

7

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

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

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

Carl, you are a god among men :prayer:

9

Heh, you’re too kind.

Expressions are pretty handy - take a minute and explore the user manual’s expression appendix if you haven’t already - lots of powerful things in there.

10

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

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

Well, sure, if you wanted to go that way… :laughing: