# SQL SELECT Query **URL:** \ **Category:** Ignition\ **Tags:** ignition80, database, vision\ **Created:** [April 26, 2022, 7:12pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893 "2022-04-26T19:12:15Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![user43](https://avatars.discourse-cdn.com/v4/letter/u/8dc957/32.png) [@user43](https://forum.inductiveautomation.com/u/user43)\ **Post date:** [April 26, 2022, 7:12pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/1 "2022-04-26T19:12:15Z")
I’m making a Report for a customer. The data is in Historian. I have made basic reports before using the designer but I’m not very good with SQL. I need a query that will grab the data for multiple flow meters , grouped totals by hour and total by day. Any help would be greatly appreciated. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/3/237fb9c6a2bb4ea55d97228229c5635d7fae30c0.png) ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [April 26, 2022, 7:17pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/2 "2022-04-26T19:17:57Z")
Querying the historian directly is complicated, best to use the built-in system function [system.tag.queryTagCalculations - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagCalculations) ---
**Author:** ![user43](https://avatars.discourse-cdn.com/v4/letter/u/8dc957/32.png) [@user43](https://forum.inductiveautomation.com/u/user43)\ **Post date:** [April 26, 2022, 8:02pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/3 "2022-04-26T20:02:33Z")
Ok, so I read the [system.tag.queryTagCalculations] and the [system.tag.queryTagHistory] and that look like what I need. How would I , or can I use this in the Reports designer or do I need to create a table on a Vision page and execute there? ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [April 26, 2022, 8:17pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/4 "2022-04-26T20:17:55Z")
Create a scripting data source in the report, there you can run your query functions and assign them to data keys like so ```auto data['flow1'] = system.tag.queryTagCalculations(.....) data['flow2'] = system.tag.queryTagCalculations(.....) ``` ---
**Author:** ![user43](https://avatars.discourse-cdn.com/v4/letter/u/8dc957/32.png) [@user43](https://forum.inductiveautomation.com/u/user43)\ **Post date:** [April 26, 2022, 10:08pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/5 "2022-04-26T22:08:02Z")
Ok, Thanks. I will give it a try! ---
**Author:** ![user43](https://avatars.discourse-cdn.com/v4/letter/u/8dc957/32.png) [@user43](https://forum.inductiveautomation.com/u/user43)\ **Post date:** [April 27, 2022, 1:17pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/6 "2022-04-27T13:17:13Z")
> [@dkhayes117](#): > > `data['flow1']` I think I'm getting closer, there may be a syntax or format error because the dataset doesn't show up in the Designer data sources. (Thanks again for your help) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/d/bd491a44e019bc8fc874a0d4d8f660fe102755cc.png) ---
**Author:** ![user43](https://avatars.discourse-cdn.com/v4/letter/u/8dc957/32.png) [@user43](https://forum.inductiveautomation.com/u/user43)\ **Post date:** [April 27, 2022, 1:32pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/7 "2022-04-27T13:32:56Z")
I made the correction but still have the syntax error marker under the def ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/2/b270b0b139f5ace438ea9518a722aa94a57a4be1.png) ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [April 27, 2022, 1:40pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/8 "2022-04-27T13:40:54Z")
Tip: post code rather than pictures of code. Use the `` code formatting button to preserve indentation and apply syntax highlighting. That way we can copy and edit it in our answers. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [April 27, 2022, 1:42pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/9 "2022-04-27T13:42:43Z")
~~I don’t see a `return` anywhere. Pretty sure that the function has to return the modified data map.~~ Also this: `data = ['Hist_dataset'] = dataset` is incorrect. I think, you may be looking for: ```auto dataset = data['Hist_dataset'] ``` However, since you’ve only posted a screen shot, I can’t see all of the code. Please post _all_ of the code using the preformated text `` otpion. ---
**Author:** ![user43](https://avatars.discourse-cdn.com/v4/letter/u/8dc957/32.png) [@user43](https://forum.inductiveautomation.com/u/user43)\ **Post date:** [April 27, 2022, 1:54pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/10 "2022-04-27T13:54:42Z")
This is the code so far. ```auto data = dataset['Hist_dataset'] endTime = system.date.now() startTime = system.date.addMinutes(endTime, -59) data['Flow1']= system.tag.queryTagCalculations(paths=['[IgnitionSQL]abiop_0401_fi'],startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="Maximum", returnFormat='Wide') data['Flow2']= system.tag.queryTagCalculations(paths=['[IgnitionSQL]cbiop_0903_fi'],startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="Maximum", returnFormat='Wide') ``` ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [April 27, 2022, 1:59pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/11 "2022-04-27T13:59:48Z")
All you need in the script is this: ```auto endTime = system.date.now() startTime = system.date.addHours(endTime,-1) data['Flow1']= system.tag.queryTagCalculations(paths=['[IgnitionSQL]abiop_0401_fi'],startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="Maximum", returnFormat='Wide') data['Flow2']= system.tag.queryTagCalculations(paths=['[IgnitionSQL]cbiop_0903_fi'],startDate=startTime, endDate=endTime, returnSize=1, aggregationMode="Maximum", returnFormat='Wide') ``` Make sure that your indentation matches on every line. (Always uses tabs or always uses spaces. You can’t mix) ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [April 27, 2022, 2:02pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/12 "2022-04-27T14:02:18Z")
> [@user43](#): > > `data = dataset['Hist_dataset']` Your problem is the first line , you are overriding the data variable used for the data sources. Do not use `data` in report scripting sources except to add keys and values to it like ```auto data['Flow1']= ... data['Flow2']= ... ``` ---
**Author:** ![user43](https://avatars.discourse-cdn.com/v4/letter/u/8dc957/32.png) [@user43](https://forum.inductiveautomation.com/u/user43)\ **Post date:** [April 27, 2022, 2:17pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/13 "2022-04-27T14:17:34Z")
Ok, I modified the code, no errors, but it still does not show up in the designer data sources, ```auto ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 27, 2022, 2:46pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/14 "2022-04-27T14:46:19Z")
Keys injected by script are not browseable in design mode. Go to preview mode to see the content in the XML. ---
**Author:** ![user43](https://avatars.discourse-cdn.com/v4/letter/u/8dc957/32.png) [@user43](https://forum.inductiveautomation.com/u/user43)\ **Post date:** [April 27, 2022, 2:49pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/15 "2022-04-27T14:49:58Z")
Yes, I can see the values from the script in the XML panel. How do I get them in to a table in my report. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 27, 2022, 2:54pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/16 "2022-04-27T14:54:58Z")
You _ **type** _ the key into the table’s properties. ---
**Author:** ![user43](https://avatars.discourse-cdn.com/v4/letter/u/8dc957/32.png) [@user43](https://forum.inductiveautomation.com/u/user43)\ **Post date:** [April 27, 2022, 3:43pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/17 "2022-04-27T15:43:46Z")
Ok, so I noticed when I went to preview It give me a null error, Is there null check I can add to the script? ```auto WARN: Error invoking script.Traceback (most recent call last): File "", line 4, in updateData TypeError: Aggregation mode "null" does not exist. ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 27, 2022, 5:55pm UTC](https://forum.inductiveautomation.com/t/sql-select-query/58893/18 "2022-04-27T17:55:14Z")
Use python’s `someThing is None` construct.