Multiple SQL statements?

Ignition
1

I have a report that is pulling a single row of data from my database (about 15 columns total). In the report I display each point of data in a table and also sum up all the data into a total displayed in the last row. The problem is that the totalizing of data is done in the report (by performing addition on keys) and if I export the data to an Excel spreadsheet (via system.dataset.exportExcel) the total is not there because it is not part of the actual dataset of the report. I need to pull the data from the database into the report dataset WITH the total sum as part of the set. I can run a normal SQL query (SELECT * FROM DB WHERE BLAH BLAH BLAH) but how do I also pull the SUM of the data into the same dataset? Any help would be greatly appreciated.

2

Hello,
I can think of a couple ways.

One way it use a UNION to combine two queries together.
Here’s a very short example:

(SELECT col1, col2, col3, 1 as sortorder FROM table)
UNION
(SELECT col1, col2, col3, 2 as sortorder FROM othertable)
ORDER BY sortorder DESC

The other idea is to run your database query in a Python script, calculate any additional data and then create a dataset with system.dataset.toDataSet and then assign the dataset to the property that needs it.

3

Call it Friday funk‚ but I just figured it out. I am now logging the total to my database. Pulls up along with the rest of the data‚ just like I wanted.

4

Or maybe you need a subquery:

SELECT col1, col2, col3, (SELECT SUM(col2) FROM table) as col4
FROM table
5

You could also use the WITH ROLLUP modifier in MySQL or MSSQL.