# Named Query Script to Add Column Values from a DB Table with Null Rows **URL:** \ **Category:** Ignition\ **Tags:** scripting\ **Created:** [January 9, 2024, 5:53pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988 "2024-01-09T17:53:00Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![Justin\_RDFTX](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin_rdftx/32/56713_2.png) [@Justin\_RDFTX](https://forum.inductiveautomation.com/u/Justin_RDFTX)\ **Post date:** [January 9, 2024, 5:53pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/1 "2024-01-09T17:53:00Z")
![Screenshot (17)](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/2/42166de9afae3ccb6fb5ec1faefe95b976d45506.png) This is what I currently have to filter for a "reason"(yellow circle), but I cannot seem to figure out how to take the sum of these filtered values to use for a chart (red circle). I have tried to Coalesce, but I am either doing it wrong or it will not work for a Named Query. Any insight into this would be greatly appreciated for I am new to scripting. ---
**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:** [January 9, 2024, 6:05pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/2 "2024-01-09T18:05:03Z")
Use your database's GROUP BY syntax. e.g., ```auto SELECT Reason, SUM([Downtime Minutes]) FROM vwIG_DownTimeReportForFilters GROUP BY [Downtime Minutes] ``` ---
**Author:** ![Justin\_RDFTX](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin_rdftx/32/56713_2.png) [@Justin\_RDFTX](https://forum.inductiveautomation.com/u/Justin_RDFTX)\ **Post date:** [January 9, 2024, 6:23pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/3 "2024-01-09T18:23:14Z")
Even running this code, I believe I am still running into the same problem with SUM(). If I understand it correctly, If you try to obtain the sum of a column that has a null value, the answer will always be null. This is the issue I am running into, but i cannot find a way to nix the null out of the SUM() equation so I can obtain the sum of the remaining numbers. ---
**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:** [January 9, 2024, 6:47pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/4 "2024-01-09T18:47:50Z")
> [@Justin\_RDFTX](#): > > If you try to obtain the sum of a column that has a null value, the answer will always be null No, the SQL standard dictates that nulls are skipped. You only get a null if all in that group are null. ---
**Author:** ![Justin\_RDFTX](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin_rdftx/32/56713_2.png) [@Justin\_RDFTX](https://forum.inductiveautomation.com/u/Justin_RDFTX)\ **Post date:** [January 9, 2024, 7:08pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/5 "2024-01-09T19:08:44Z")
When I try to run the script I only get null in return, but if I take the SUM() expression out then I get my filtered list when I run the script. What am I missing? ---
**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:** [January 9, 2024, 7:11pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/6 "2024-01-09T19:11:52Z")
Show your actual SQL. Keep in mind that the output column list of a query that includes grouping typically must only consist of aggregate functions, except for the grouping columns/keys themselves. ---
**Author:** ![Justin\_RDFTX](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin_rdftx/32/56713_2.png) [@Justin\_RDFTX](https://forum.inductiveautomation.com/u/Justin_RDFTX)\ **Post date:** [January 9, 2024, 9:42pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/7 "2024-01-09T21:42:51Z")
I tried an iteration of the aforementioned code. It seems to be doing what I want currently, but I may have to get some advice when I try to filter by dates to narrow the list. I do appreciate all of the feedback thus far. ---
**Author:** ![Justin\_RDFTX](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin_rdftx/32/56713_2.png) [@Justin\_RDFTX](https://forum.inductiveautomation.com/u/Justin_RDFTX)\ **Post date:** [January 10, 2024, 4:58pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/8 "2024-01-10T16:58:51Z")
Ok, so I have gotten previous kinks out of the process I was trying to execute before, but I have run into another problem. I have been able to filter dates and add the column, but now I cannot figure out how to Filter the addition where it only adds rows with certain reason codes. When I execute the code, it gives me the total for all rows between the filtered dates. Here is my code: ![Screenshot (20)](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/d/5d27fd2b02701466264dee06f528cfaaf224e94f.png) Here is the result: ![Screenshot (21)](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/2/12cca32f9ec5c9e2d7d9e18af10644d557ddd9c6.png) Ant this is the table I'm trying to pull the info from (filtered dates are highlighted): ![Screenshot (22)](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/0/a07b4e47c0679c8867372bef00a58ee2a922ad54.png) I am trying to figure out how to only include the rows that say 'Electrical' and have them be calculated independently from the rest of the rows between the filtered dates. I know the data type for the "Reason" column (column with Electrical, Mechanical, Raw Material, and Operational) is an 'nvarchar,' so I am not completely sure if my 'WHERE' clause is filtering properly. Thanks for the help. ---
**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:** [January 10, 2024, 5:06pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/9 "2024-01-10T17:06:09Z")
Please post code - not pictures of code - so we don't have to type it all out again ... Your first line is wrong. Try, ```auto SELECT Reason, SUM([DTMIN]) FROM vwIG... WHERE Reason = 'Electrical' AND ( (...) OR (...) OR (...) ) ``` ---
**Author:** ![Justin\_RDFTX](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin_rdftx/32/56713_2.png) [@Justin\_RDFTX](https://forum.inductiveautomation.com/u/Justin_RDFTX)\ **Post date:** [January 10, 2024, 5:16pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/10 "2024-01-10T17:16:05Z")
Sorry about that, I will keep it in mind for the future. I tried it but now I am getting an error. ```auto SELECT Reason, SUM([DTMIN]) FROM vwIG_DownTimeDetailsByPID WHERE Reason = 'Electrical' AND ( ([Trans Date] BETWEEN '2024-01-02'AND '2024-01-09') OR ([Trans Date] BETWEEN '2024-01-02' AND '2024-01-09') OR ([Trans Date] <= '2024-01-02' AND [Trans Date] >= '2024-01-09') ) ``` Error: com.microsoft.sqlserver.jdbc.SQLServerException: Column 'vwIG\_DownTimeDetailsByPID.REASON' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. ---
**Author:** ![Justin\_RDFTX](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin_rdftx/32/56713_2.png) [@Justin\_RDFTX](https://forum.inductiveautomation.com/u/Justin_RDFTX)\ **Post date:** [January 10, 2024, 5:16pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/11 "2024-01-10T17:16:40Z")
I did put the appropriate spaces, just doesn't reflect with copy paste ---
**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:** [January 10, 2024, 5:26pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/12 "2024-01-10T17:26:41Z")
There was an example of the GROUP BY in my original post. ```auto SELECT Reason, SUM([DTMIN]) FROM vwIG_DownTimeDetailsByPID WHERE Reason = 'Electrical' AND ( ([Trans Date] BETWEEN '2024-01-02'AND '2024-01-09') OR ([Trans Date] BETWEEN '2024-01-02' AND '2024-01-09') OR ([Trans Date] <= '2024-01-02' AND [Trans Date] >= '2024-01-09') ) GROUP BY Reason ``` See also, [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831) ---
**Author:** ![Justin\_RDFTX](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin_rdftx/32/56713_2.png) [@Justin\_RDFTX](https://forum.inductiveautomation.com/u/Justin_RDFTX)\ **Post date:** [January 10, 2024, 5:28pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/13 "2024-01-10T17:28:03Z")
Update: I re-evaluated the code I posted before and realized I had a duplicate line. I deleted the duplicate, and it is working now. Thanks for the insight. ---
**Author:** ![Justin\_RDFTX](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin_rdftx/32/56713_2.png) [@Justin\_RDFTX](https://forum.inductiveautomation.com/u/Justin_RDFTX)\ **Post date:** [January 10, 2024, 5:30pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/14 "2024-01-10T17:30:13Z")
This also works and looks much better than mine. Thank you very much. ---
**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:** [January 10, 2024, 5:32pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/15 "2024-01-10T17:32:52Z")
> [@Transistor](#): > > `OR ([Trans Date] <= '2024-01-02' AND [Trans Date] >= '2024-01-09')` I presume that this is just sample code but there is no date that will satisfy that part of the query! Tip: Do the `>=` before the `<=` to make it easier / more logical to read. Or use SQL's BETWEEN function. > **[SQL BETWEEN Operator](https://www.w3schools.com/sql/sql_between.asp)** > > W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more. ---
**Author:** ![Justin\_RDFTX](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justin_rdftx/32/56713_2.png) [@Justin\_RDFTX](https://forum.inductiveautomation.com/u/Justin_RDFTX)\ **Post date:** [January 10, 2024, 5:35pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/16 "2024-01-10T17:35:36Z")
When I found this part of the code it was ranging between two separate columns instead of one like I am. I just copy pasted it and changed the params to see if it would work not realizing that there would be a duplicate. I'm not sure why but as soon as I got rid of the duplicate, the code was happy. ---
**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:** [January 10, 2024, 5:48pm UTC](https://forum.inductiveautomation.com/t/named-query-script-to-add-column-values-from-a-db-table-with-null-rows/83988/17 "2024-01-10T17:48:01Z")
> [@Transistor](#): > > Or use SQL's BETWEEN function Ew. Or not. Between includes the endpoint value. Half-open intervals allow for fractional time periods at an endpoint without specifying the trailing zeros to the full column precision. For example, this: ```auto ... WHERE t_stamp BETWEEN '2024-01-01T00:00:00' AND '2024-01-01T23:59:59' ``` will miss any data points that have non-zero milliseconds in the last second. Using this: ```auto ... WHERE t_stamp >= '2024-01-01' AND t_stamp < '2024-01-02' ``` will get every value with a timestamp on January 1st, no matter the precision of the `t_stamp` column. Note that the start is checked with greater-than-or-equals but the end is checked with less-than. (Half-open means one end of an interval includes equal.) That Ignition's historian doesn't require half-open intervals for its queries is one of its most annoying flaws, fwiw. (It also means that its API is stuck on milliseconds, as any attempt to switch to something more precise will break all current users.)