# system.db.runPrepQuery - GROUP BY **URL:** \ **Category:** Ignition\ **Tags:** ignition80\ **Created:** [June 27, 2022, 1:54pm UTC](https://forum.inductiveautomation.com/t/system-db-runprepquery-group-by/61240 "2022-06-27T13:54:36Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Paul.M](https://avatars.discourse-cdn.com/v4/letter/p/c0e974/32.png) [@Paul.M](https://forum.inductiveautomation.com/u/Paul.M)\ **Post date:** [June 27, 2022, 1:54pm UTC](https://forum.inductiveautomation.com/t/system-db-runprepquery-group-by/61240/1 "2022-06-27T13:54:36Z")
Hello, I try to use system.db.runPrepQuery and add a 'GROUP BY' CLAUSE, script without group by works fine. Without group by ```param data['a'] = system.db.runPrepQuery("SELECT AREA, DATEPART(week, CREATED), STATUS_TEXT FROM TABLE WHERE STATUS_TEXT = 'Open' AND DATEPART(week, CREATED) LIKE ? ", [param] ) ``` With GROUP BY ```auto param = '%' + param + '%' group = 'GROUP BY AREA, STATUS_TEXT' data['a'] = system.db.runPrepQuery("SELECT AREA, DATEPART(week, CREATED), STATUS_TEXT FROM TABLE WHERE STATUS_TEXT = 'Open' AND DATEPART(week, CREATED) LIKE ? ", [param], [group] ) ``` error: > com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 32, in updateData at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepQuery(AbstractDBUtilities.java:302) at jdk.internal.reflect.GeneratedMethodAccessor402.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepQuery(SELECT COUNT(AREA), AREA, DATEPART(week, CREATED), STATUS\_TEXT FROM TABLE WHERE STATUS\_TEXT = 'Open' AND DATEPART(week, CREATED) LIKE ? , db.user.name, [%26%GROUP BY AREA, STATUS\_TEXT], ) ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 27, 2022, 2:01pm UTC](https://forum.inductiveautomation.com/t/system-db-runprepquery-group-by/61240/2 "2022-06-27T14:01:47Z")
First thing is your params should be in one list together like `system.db.runPrepQuery("SELECT * FROM someTable WHERE param1=? AND param2=?",[param1, param2])` Second thing is since your group by statement is hardcoded `GROUP BY AREA, STATUS_TEXT` I don’t see any reason to include it as a param instead of just hardcoding it into your query. Params are represented by `?` in your query and get substituted/sanitized for you, you don’t have a `?` correspodning to where the group clause would go, but again, I think since that never changes it’s a misuse of params to inject it like that. Try ```auto data['a'] = system.db.runPrepQuery("SELECT AREA, DATEPART(week, CREATED), STATUS_TEXT FROM TABLE WHERE STATUS_TEXT = 'Open' AND DATEPART(week, CREATED) LIKE ? GROUP BY AREA, STATUS_TEXT", [param]) ``` ---
**Author:** ![kcollins](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kcollins/32/19646_2.png) [@kcollins](https://forum.inductiveautomation.com/u/kcollins)\ **Post date:** [June 28, 2022, 7:55pm UTC](https://forum.inductiveautomation.com/t/system-db-runprepquery-group-by/61240/3 "2022-06-28T19:55:29Z")
Does this query (that you’re adapting for use in Ignition) run fine in SSMS (I’m assuming SQL Server based on `DATEPART` function)? I imagine you might run into a complaint about your `GROUP BY` complaining about `DATEPART(week, CREATED)` being _invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause_. ---
**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:** [June 28, 2022, 8:39pm UTC](https://forum.inductiveautomation.com/t/system-db-runprepquery-group-by/61240/4 "2022-06-28T20:39:46Z")
> [@bkarabinchak.psi](#): > > I think since that never changes it’s a misuse of params to inject it like that. As @bkarabinchak.psi pointed out, you can't substitute schema objects in prepared statements. That means any (Table Names, Column names, etc...). That isn't what is causing this error though (I'm guess as you haven't shared the entire stack trace). More likely is the fact that you are providing `[group]` to the datasource parameter of the function, which I am guessing isn't going to work. See @bkarabinchak.psi post for the correct structure when calling `runPrepQuery()`. If you do need the GROUP BY clause to be dynamic, this can be accomplished using a Named Query with a QueryString parameter and calling `system.db.runNamedQuery()`. All SQL Injection risks apply here so do not allow for user input into the query string parameter. ---
**Author:** ![Paul.M](https://avatars.discourse-cdn.com/v4/letter/p/c0e974/32.png) [@Paul.M](https://forum.inductiveautomation.com/u/Paul.M)\ **Post date:** [June 28, 2022, 8:57pm UTC](https://forum.inductiveautomation.com/t/system-db-runprepquery-group-by/61240/5 "2022-06-28T20:57:03Z")
Query work fine in SSMS, was only a bad schema object … ☹