# MySQL Query Help for report **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [September 22, 2025, 7:45am UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823 "2025-09-22T07:45:01Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [September 22, 2025, 7:45am UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823/1 "2025-09-22T07:45:01Z")
I am generating a report on production. I have logged in a DB when the machine is in automatic, batch count, date\_time, product and a few others. One thing I want to do is generate an index for a for loop to get the unique batches for that day. The issue is the batch count isn’t reliably logged, sometimes it skips a number and others it logs the same number multiple rows. If it were reliable I would do: `select count(automatic) from table where batch_count = 1` However as it can sometimes skip 1 or else have multiple 1’s for batch count, I need to try a different strategy. Think I might need a sub query like: `select count(automatic) from table where (select batch_count where batch_count between 1 and 5 order by batch count limit 1)` Just don’t seem to be getting the syntax right… any pointers? ---
**Author:** ![David\_Stone](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/david_stone/32/79749_2.png) [@David\_Stone](https://forum.inductiveautomation.com/u/David_Stone)\ **Post date:** [September 22, 2025, 8:05am UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823/2 "2025-09-22T08:05:27Z")
Which database are you using? -- MySQL in title, I cant read. ---
**Author:** ![David\_Stone](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/david_stone/32/79749_2.png) [@David\_Stone](https://forum.inductiveautomation.com/u/David_Stone)\ **Post date:** [September 22, 2025, 8:14am UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823/3 "2025-09-22T08:14:52Z")
```sql select count(automatic) from table where (select batch_count where batch_count between 1 and 5 order by batch count limit 1) ``` Your `where` clause doesn't evaluate to a boolean. If you look at the subquery: ```sql select batch_count where batch_count between 1 and 5 order by batch count limit 1 ``` This will always return the lowest batch count number if it is between 1 and 5. So, if you sub this into the original query you get this: ```sql select count(automatic) from table where 1 ``` Does this modified query match what you thought you were doing? ```sql select count(automatic) from table where batch_count = (select batch_count where batch_count between 1 and 5 order by batch count limit 1) ``` ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [September 22, 2025, 8:17am UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823/4 "2025-09-22T08:17:53Z")
> [@David\_Stone](#): > > Does this modified query match what you thought you were doing? > > ```python > select count(automatic) from table where batch_count = (select batch_count where batch > > ``` Yes, might have to wait a while for them to get onto the 2nd batch of the day to test it ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [September 22, 2025, 8:18am UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823/5 "2025-09-22T08:18:54Z")
> [@David\_Stone](#): > > This will always return the lowest batch count number if it is between 1 and 5. That’s what I want, select 1 if it is 1, if not select 2 if that’s the next available number. Also, if there are multiple 1’s, only count 1. ---
**Author:** ![David\_Stone](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/david_stone/32/79749_2.png) [@David\_Stone](https://forum.inductiveautomation.com/u/David_Stone)\ **Post date:** [September 22, 2025, 8:19am UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823/6 "2025-09-22T08:19:57Z")
> [@Matrix\_Engineering](#): > > The issue is the batch count isn’t reliably logged, sometimes it skips a number and others it logs the same number multiple rows. I am interested in this bit, what is your mechanism for logging this to database? ---
**Author:** ![David\_Stone](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/david_stone/32/79749_2.png) [@David\_Stone](https://forum.inductiveautomation.com/u/David_Stone)\ **Post date:** [September 22, 2025, 8:22am UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823/7 "2025-09-22T08:22:21Z")
It's really hard to visualise whats in your table, are you able to show sample data? ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [September 22, 2025, 8:23am UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823/8 "2025-09-22T08:23:50Z")
A gateway scheduled script, running at 1s intervals, if machine bit is automatic log the data. ---
**Author:** ![David\_Stone](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/david_stone/32/79749_2.png) [@David\_Stone](https://forum.inductiveautomation.com/u/David_Stone)\ **Post date:** [September 22, 2025, 8:26am UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823/9 "2025-09-22T08:26:07Z")
Might I suggest a gateway tag change script? The tag change script will process the published change, so it should capture the change faster and more reliably than the 1s resolution you are getting right now. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [September 22, 2025, 8:30am UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823/10 "2025-09-22T08:30:58Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/6/e6cd980d8c3c605058642d6f7539d32bc17033cf.png) They haven’t started production yet today, never mind get onto 2nd batch ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [September 22, 2025, 3:49pm UTC](https://forum.inductiveautomation.com/t/mysql-query-help-for-report/110823/11 "2025-09-22T15:49:12Z")
> [@David\_Stone](#): > > Does this modified query match what you thought you were doing? > > ```python > select count(automatic) from table where batch_count = (select batch_count where batch_c > > ``` This is the right syntax, I get a result back, however the logic still isn’t exactly what I want. If I run the subquery as between 4 and 5 I get a count of 6, if I run it between 3 and 9 I get a count of 9. Did some google’ing and it was suggesting that using a limit in a sub query isn’t allowed, however I think that relates to an older version of MySQL as I don’t get an explicit error. I am using 8.0.42.