Count Occurrences in SQL

Ignition
1

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.

SELECT ActiveWorkOrder_num,Record_ID,TestResults_Bad_num
FROM LeakTest_Part_Tracking
WHERE ActiveWorkOrder_num = :WorkOrder
2

Please change your formatting to "Preformatted Text", to make a code block others can copy and paste from.

1 Like
3

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?

4

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.

5 
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
6

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.

7

what database flavor are you using? Oh, I guess its MSSQL, from the error…

8

I am using a Microsoft SQL server that is hosted locally on the same machine Ignition is running on.

9

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]

1 Like
10

This is what I get when I test the query.

image
image3063×315 39.3 KB

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?

11

You will have two records if you have failures and passes with that query.

To combine to one record just do something like

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
2 Likes
12

This works great! Thank you.

13

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 ↩︎

1 Like
14

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.)