# Ignition SQL query dataset expression function

**URL:** <https://forum.inductiveautomation.com/t/ignition-sql-query-dataset-expression-function/75922>\
**Category:** Ignition\
**Tags:** scripting, database\
**Created:** [July 5, 2023, 8:11am UTC](https://forum.inductiveautomation.com/t/ignition-sql-query-dataset-expression-function/75922 "2023-07-05T08:11:19Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jens\_Brachmann](https://avatars.discourse-cdn.com/v4/letter/j/65b543/32.png) [@Jens\_Brachmann](https://forum.inductiveautomation.com/u/Jens_Brachmann)\
**Post date:** [July 5, 2023, 8:11am UTC](https://forum.inductiveautomation.com/t/ignition-sql-query-dataset-expression-function/75922/1 "2023-07-05T08:11:19Z")

</div>

Hello all,

I would like to implement the following query in IGNITION and am encountering problems.

Using the following query I get data sorted as follows and stored in the tag RAW\_DATA as dataset.

"_SELECT ORDERNR, PIECES, DURATION, StartDate&Time, EndDate&Time_  
_FROM DATABASE_"

Dataset:  
ORDERNR | PIECES | DURATION | StartTDate| StartTime EndDate&Time  
0101 | 0 | 100 | 20230630 | 063000 | 20230630 | 083000 |  
0101 | 10 | 250 | 20230630 | 103000 | 20230630 | 123000 |  
0101 | 5 | 150 | 20230631 | 093000 | 20230631 | 103000 |  
0102 | 20 | 200 | 20230630 | 063000 | 20230630 | 083000 |

I would like to summarize the data in an EXPRESSION function as follows.

Count all PIECES & DURATION with the same ORDERNR together.  
Filter earliest StartTime and StartTime & latest EndDate EndTime of an ORDERNR.

The summarized output should then be as follows.

ORDERNR, PIECES, DURATION, StartDate, StartTime, EndDate, EndTime  
0101 | 15 | 500 | 20230630 | 063000 | 20230631 | 103000 |  
0102 | 20 | 200 | 20230630 | 063000 | 20230630 | 083000 |

With my approach I get unfortunately all the time an error message.

Could someone help me?

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [July 5, 2023, 8:42am UTC](https://forum.inductiveautomation.com/t/ignition-sql-query-dataset-expression-function/75922/2 "2023-07-05T08:42:29Z")

</div>

You should probably do this directly in the query.  
It's not clear what values should be selected for the dates, can you clarify ?

try something like this:

```sql
select
  ordernr,
  sum(pieces) as 'sum pieces',
  sum(duraction) as 'sum duration',
  min(StartDate) as StartDate,
  min(StartTime) as StartTime
  max(EndDate) as EndDate,
  max(EndTime) as EndTime
from
  database
group by
  ordernr

```

if you REALLY want an expression... frankly I'd use a script. I'm sure @pturmel has exactly what's needed to do this in an expression with his simulation aids module, but if you're not using it, use a script.

> [@Jens\_Brachmann](#):
>
> I get unfortunately all the time an error message.

When you have an error... it might be a good idea to tell us what it is if you want help fixing it. We're not seers.

Also, you can use markdown's table syntax when you want to display a table:

> | col1 | col2 | col3 |  
> | -- | -- | -- |  
> | foo1 | foo2 | foo3 |  
> | bar1 | bar2 | bar3 |

will be formatted like this :

| col1 | col2 | col3 |
| --- | --- | --- |
| foo1 | foo2 | foo3 |
| bar1 | bar2 | bar3 |