# Multiple SQL statements?

**URL:** <https://forum.inductiveautomation.com/t/multiple-sql-statements/8196>\
**Category:** Ignition\
**Created:** [March 21, 2014, 9:24pm UTC](https://forum.inductiveautomation.com/t/multiple-sql-statements/8196 "2014-03-21T21:24:47Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![pjgonzales](https://avatars.discourse-cdn.com/v4/letter/p/59ef9b/32.png) [@pjgonzales](https://forum.inductiveautomation.com/u/pjgonzales)\
**Post date:** [March 21, 2014, 9:24pm UTC](https://forum.inductiveautomation.com/t/multiple-sql-statements/8196/1 "2014-03-21T21:24:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [March 21, 2014, 9:51pm UTC](https://forum.inductiveautomation.com/t/multiple-sql-statements/8196/2 "2014-03-21T21:51:43Z")

</div>

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:

```auto
(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.

---

<div class="post-metadata">

**Author:** ![pjgonzales](https://avatars.discourse-cdn.com/v4/letter/p/59ef9b/32.png) [@pjgonzales](https://forum.inductiveautomation.com/u/pjgonzales)\
**Post date:** [March 21, 2014, 10:10pm UTC](https://forum.inductiveautomation.com/t/multiple-sql-statements/8196/3 "2014-03-21T22:10:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [March 21, 2014, 10:12pm UTC](https://forum.inductiveautomation.com/t/multiple-sql-statements/8196/4 "2014-03-21T22:12:15Z")

</div>

Or maybe you need a subquery:

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

---

<div class="post-metadata">

**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\
**Post date:** [March 21, 2014, 11:21pm UTC](https://forum.inductiveautomation.com/t/multiple-sql-statements/8196/5 "2014-03-21T23:21:03Z")

</div>

You could also use the WITH ROLLUP modifier in [MySQL](http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html) or [MSSQL](http://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx).