# Named Query reports Invalid Column due to aggregates and the column not being in a GROUP BY clause... except I'm not trying to select the column

**URL:** <https://forum.inductiveautomation.com/t/named-query-reports-invalid-column-due-to-aggregates-and-the-column-not-being-in-a-group-by-clause-except-im-not-trying-to-select-the-column/25981>\
**Category:** Ignition Early Access\
**Tags:** namedqueries\
**Created:** [June 20, 2019, 1:23pm UTC](https://forum.inductiveautomation.com/t/named-query-reports-invalid-column-due-to-aggregates-and-the-column-not-being-in-a-group-by-clause-except-im-not-trying-to-select-the-column/25981 "2019-06-20T13:23:29Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![md.szyman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/md.szyman/32/9240_2.png) [@md.szyman](https://forum.inductiveautomation.com/u/md.szyman)\
**Post date:** [June 20, 2019, 1:23pm UTC](https://forum.inductiveautomation.com/t/named-query-reports-invalid-column-due-to-aggregates-and-the-column-not-being-in-a-group-by-clause-except-im-not-trying-to-select-the-column/25981/1 "2019-06-20T13:23:29Z")

</div>

I'm running a query to generate a histogram from some numeric columns in a table. Here's the query:

```sql
SELECT
	FLOOR((stops.stop_duration/:downtimeDivisor)/:bucketWidth)*:bucketWidth AS downtime,
	COUNT(*) AS count
FROM stop_history AS stops
WHERE stops.downtime_start BETWEEN :startDate AND :endDate
		AND (stops.first_fault NOT IN {hiddenFaults} OR :excludeHidden=0)
GROUP BY FLOOR((stops.stop_duration/:downtimeDivisor)/:bucketWidth)*:bucketWidth
ORDER BY FLOOR((stops.stop_duration/:downtimeDivisor)/:bucketWidth)*:bucketWidth

```

And the parameters are defined as such:

- `startDate` = 'May 1, 2019'
- `endDate` = 'June 20, 2019'
- `bucketWidth` = 0.5
- `downtimeDivisor` = 3600.0
- `hiddenFaults` evaluates to a list of strings
- `excludeHidden` = false

I've used these types of parameters many times before in the same way, so I doubt they're the source of the problem.

When I run the query, I get this error in Ignition:

> com.microsoft.sqlserver.jdbc.SQLServerException: Column 'stop\_history.stop\_duration' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm not trying to do a select on that column anywhere in the query, so I'm very confused.

However, if I run the exact same query (replacing the parameters with variables and the `{hiddenFaults}` with a hardcoded list) in SQL Server Management Studio, it runs without any issues.

If I add `stop_history.stop_duration` to the `GROUP BY` clause, I get the error:

> The multi-part identifier "stop\_history.stop\_duration" could not be bound.

And if I add `stops.stop_duration` to the `GROUP BY` clause, the query runs, but the data is grouped entirely wrong.

Is Ignition doing something I'm not aware of or is it somehow running the query differently than SQL Management Studio does?

---

<div class="post-metadata">

**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\
**Post date:** [June 20, 2019, 2:00pm UTC](https://forum.inductiveautomation.com/t/named-query-reports-invalid-column-due-to-aggregates-and-the-column-not-being-in-a-group-by-clause-except-im-not-trying-to-select-the-column/25981/2 "2019-06-20T14:00:01Z")

</div>

You are selecting that column:

```auto
SELECT
	FLOOR((stops.stop_duration/:downtimeDivisor)/:bucketWidth)*:bucketWidth AS downtime

```

SQL will first select it, then do the calculation, but you’re grouping on a calculation so it cannot be selected.

The solution here it to make a nested query: first query the calculated values into a temporary table (named “t” in the example), then group on those values and display them.

```auto
SELECT COUNT(t.downtime), t.downtime FROM
(
    SELECT
	FLOOR((stops.stop_duration/:downtimeDivisor)/:bucketWidth)*:bucketWidth AS downtime,
    FROM stop_history AS stops
    WHERE stops.downtime_start BETWEEN :startDate AND :endDate
		AND (stops.first_fault NOT IN {hiddenFaults} OR :excludeHidden=0)
) t
GROUP BY t.downtime
ORDER BY t.downtime

```

---

<div class="post-metadata">

**Author:** ![md.szyman](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/md.szyman/32/9240_2.png) [@md.szyman](https://forum.inductiveautomation.com/u/md.szyman)\
**Post date:** [June 20, 2019, 2:02pm UTC](https://forum.inductiveautomation.com/t/named-query-reports-invalid-column-due-to-aggregates-and-the-column-not-being-in-a-group-by-clause-except-im-not-trying-to-select-the-column/25981/3 "2019-06-20T14:02:38Z")

</div>

Well that’s weird.

But it worked, it returned the exact same results as the query being run in the Management Studio.

I’m still very confused as to why it works there but not in Ignition, but this solution certainly worked.

Thanks!

---

<div class="post-metadata">

**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\
**Post date:** [June 20, 2019, 2:05pm UTC](https://forum.inductiveautomation.com/t/named-query-reports-invalid-column-due-to-aggregates-and-the-column-not-being-in-a-group-by-clause-except-im-not-trying-to-select-the-column/25981/4 "2019-06-20T14:05:20Z")

</div>

There may be a difference when you pass literal values.  
Then the SQL server can check the calculations are equal, and do them in advance.  
But when the values are passed as variables, the SQL server cannot assume they’re equal, and has to calculate them 3 times, possibly giving columns that don’t match grouping.

In any case, the subquery is cleaner IMO.