# Count Occurrences in SQL **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [September 23, 2025, 12:13pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886 "2025-09-23T12:13:25Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![bshriner](https://avatars.discourse-cdn.com/v4/letter/b/46a35a/32.png) [@bshriner](https://forum.inductiveautomation.com/u/bshriner)\ **Post date:** [September 23, 2025, 12:13pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/1 "2025-09-23T12:13:25Z")
I want to be able to count how many times a 0 or a 1 appears in the TestResults\_Bad\_num. I want it to look through all the rows that have imported and return a variable hopefully. Or something that I can bind to a custom property so I can display it in a label. This is the query I have so far. For context WorkOrder is a parameter I have that is linked to a custom properly. In a perfect world TestResults\_Bad\_num wouldnt be displayed on the table either. ```sql SELECT ActiveWorkOrder_num,Record_ID,TestResults_Bad_num FROM LeakTest_Part_Tracking WHERE ActiveWorkOrder_num = :WorkOrder ``` ---
**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:** [September 23, 2025, 12:24pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/2 "2025-09-23T12:24:19Z")
Please change your formatting to "Preformatted Text", to make a code block others can copy and paste from. ---
**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:** [September 23, 2025, 12:31pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/3 "2025-09-23T12:31:56Z")
Is `TestResults_Bad_num` either a 0 or 1, or is it a binary string like `001011101`? Counting the number of ones per row for the latter, or counting rows that are 1? ---
**Author:** ![bshriner](https://avatars.discourse-cdn.com/v4/letter/b/46a35a/32.png) [@bshriner](https://forum.inductiveautomation.com/u/bshriner)\ **Post date:** [September 23, 2025, 12:33pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/4 "2025-09-23T12:33:39Z")
It is either a 1 or a 0. 0 means that the part passed and a 1 means that it failed. I would want it to count the rows are are 0 and 1. I want to display the total number of parts that passed and failed. ---
**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:** [September 23, 2025, 12:37pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/5 "2025-09-23T12:37:14Z")
```sql SELECT ActiveWorkOrder_num, Record_ID, case when TestResults_Bad_num = 0 then 'Passed' else 'Failed' end as Result, count(*) as total FROM LeakTest_Part_Tracking WHERE ActiveWorkOrder_num = :WorkOrder GROUP BY TestResults_Bad_num ``` ---
**Author:** ![bshriner](https://avatars.discourse-cdn.com/v4/letter/b/46a35a/32.png) [@bshriner](https://forum.inductiveautomation.com/u/bshriner)\ **Post date:** [September 23, 2025, 12:40pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/6 "2025-09-23T12:40:09Z")
When I test that query I get the following error message `com.microsoft.sqlserver.jdbc.SQLServerException: Column 'LeakTest_Part_Tracking.ActiveWorkOrder_num' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` ---
**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:** [September 23, 2025, 12:40pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/7 "2025-09-23T12:40:44Z")
what database flavor are you using? Oh, I guess its MSSQL, from the error… ---
**Author:** ![bshriner](https://avatars.discourse-cdn.com/v4/letter/b/46a35a/32.png) [@bshriner](https://forum.inductiveautomation.com/u/bshriner)\ **Post date:** [September 23, 2025, 12:43pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/8 "2025-09-23T12:43:18Z")
I am using a Microsoft SQL server that is hosted locally on the same machine Ignition is running on. ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [September 23, 2025, 12:45pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/9 "2025-09-23T12:45:38Z")
`SELECT [TestResults_Bad_Num] as PassFail,COUNT([ActiveWorkOrder_num]) as Ttl,ActiveWorkOrder_num` `FROM [LeakTest_Part_Tracking]` `WHERE ActiveWorkOrder_num = :WorkOrder` `GROUP BY [TestResults_Bad_Num],[ActiveWorkOrder_num]` ---
**Author:** ![bshriner](https://avatars.discourse-cdn.com/v4/letter/b/46a35a/32.png) [@bshriner](https://forum.inductiveautomation.com/u/bshriner)\ **Post date:** [September 23, 2025, 1:04pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/10 "2025-09-23T13:04:28Z")
This is what I get when I test the query. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/b/eb7c87277cf9910a4112f45e94251974c9d9e3ad.png) There are 40 Passes so I’m assuming the Ttl is how many passed and the PassFail is how many failed. Would it maybe be easier to have a separate query that handles this totaling and another one that handles what is going to be displayed? ---
**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\ **Post date:** [September 23, 2025, 1:28pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/11 "2025-09-23T13:28:36Z")
You will have two records if you have failures and passes with that query. To combine to one record just do something like ```python SELECT COUNT(CASE WHEN TestResults_Bad_num = 0 THEN 1 END) as Passed, COUNT(CASE WHEN TestResults_Bad_num = 1 THEN 1 END) as Failed FROM [LeakTest_Part_Tracking] WHERE ActiveWorkOrder_num = :WorkOrder ``` ---
**Author:** ![bshriner](https://avatars.discourse-cdn.com/v4/letter/b/46a35a/32.png) [@bshriner](https://forum.inductiveautomation.com/u/bshriner)\ **Post date:** [September 23, 2025, 1:32pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/12 "2025-09-23T13:32:31Z")
This works great! Thank you. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [September 23, 2025, 3:35pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/13 "2025-09-23T15:35:24Z")
> [@bshriner](#): > > I am using a Microsoft SQL server that is hosted locally on the same machine Ignition is running on. Note: This is okay for testing purposes, but you really don't want to do this in production. Ignition and SQL databases have similar IO patterns and tend to try to "fight" for the same kinds of resources (CPU time, memory) when they're installed in the same OS instance. It's generally recommended to keep them isolated from each other in separate VMs \[1\]/containers/physical hardware. * * * 1. with dedicated resource allocations ---
**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:** [September 23, 2025, 3:53pm UTC](https://forum.inductiveautomation.com/t/count-occurrences-in-sql/110886/14 "2025-09-23T15:53:17Z")
> [@paul-griffith](#): > > have similar IO pattern Um, **dis** -similar. And it is the CPU utilization that I've noted as the biggest problem: Ignition needs some idle time to keep latency down, where a DB will consume all available CPU. (RAM is a problem, too, if you don't tell Ignition to grab what it needs up front.)