# Ignition report, SQL query help

**URL:** <https://forum.inductiveautomation.com/t/ignition-report-sql-query-help/83453>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [December 22, 2023, 1:00am UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-help/83453 "2023-12-22T01:00:11Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Andrew\_Taylor](https://avatars.discourse-cdn.com/v4/letter/a/cdc98d/32.png) [@Andrew\_Taylor](https://forum.inductiveautomation.com/u/Andrew_Taylor)\
**Post date:** [December 22, 2023, 1:00am UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-help/83453/1 "2023-12-22T01:00:11Z")

</div>

I am working on a proof of concept for using an Ignition report.

I have two conveyors, one 2M, one 5M. Each conveyor has a prox sensor that a robot picks from.

I have Ignition logging to a tall table every second the status of the sensors (via a gateway script).

What I would like to do is have an Ignition report summary showing machine activity based on the sensor data.

I have completed IU, so really it is just the SQL I need help with. I am using MariaDB.

This is what raw data looks like.

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/1/f1cc73839f7cb4c6fecb335a524236bc59ab16c8.png)

The datatype of the sensor column is tinyint, not sure if this is the most suitable or not.

The ultimate goal is a bar chart showing activity, broken down by hour.

Note that the cycle time of the conveyor indexing after robot has picked, is about 3 seconds, so for a pick the raw data in one column will look something like:

1  
1  
1  
1  
1  
0  
0  
0  
1  
1  
1  
1  
1

I can do the SQL basics for getting the raw data, and am aware of group by etc functions, but a steer in the right direction would be appreciated. Long term, it is perhaps better to integrate these mettrics into PLC counters, but for now I don't have access to that.

---

<div class="post-metadata">

**Author:** ![franklepkowski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/franklepkowski/32/4808_2.png) [@franklepkowski](https://forum.inductiveautomation.com/u/franklepkowski)\
**Post date:** [December 22, 2023, 1:47am UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-help/83453/2 "2023-12-22T01:47:32Z")

</div>

Your tables are going to grow at a very fast rate using this method. Why not utilize a transaction group or insert query to insert a pick event into your sql table instead?

As far as the queries you will need, there are tons of resources out on the web to reference.

Frank

---

<div class="post-metadata">

**Author:** ![Andrew\_Taylor](https://avatars.discourse-cdn.com/v4/letter/a/cdc98d/32.png) [@Andrew\_Taylor](https://forum.inductiveautomation.com/u/Andrew_Taylor)\
**Post date:** [December 22, 2023, 4:15am UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-help/83453/3 "2023-12-22T04:15:15Z")

</div>

> [@franklepkowski](#):
>
> Your tables are going to grow at a very fast rate using this method. Why not utilize a transaction group or insert query to insert a pick event into your sql table instead?

1. Don't want to miss an event.
2. Storage is cheap.

> [@franklepkowski](#):
>
> As far as the queries you will need, there are tons of resources out on the web to reference.

```auto
SELECT site, ffr as "2m", COUNT(ffr) as "2m qty", por as "5m", COUNT(por) as "5m qty", dt as "dateTime" FROM phgt
WHERE dt >= '2023-12-18 08:00:00'
  AND dt < '2023-12-21 20:00:00'
GROUP by ffr, por, hour(dt), day(dt)
ORDER by idphgt desc

```

The grouping I have shows the same quantity for 2m and 5m, which isn't right:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/2/0262851db01f6d2129d72f0a676c5284146b2371.png)

Also I think I need to build logic into it, to detect a transition from 0 to 1 as one event.

---

<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:** [December 22, 2023, 8:15am UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-help/83453/4 "2023-12-22T08:15:45Z")

</div>

> [@Andrew\_Taylor](#):
>
> The grouping I have shows the same quantity for 2m and 5m, which isn't right

Well you're counting the number of rows in both cases, it's always going to be the same. Did you mean to use `sum()` instead ?

---

<div class="post-metadata">

**Author:** ![Andrew\_Taylor](https://avatars.discourse-cdn.com/v4/letter/a/cdc98d/32.png) [@Andrew\_Taylor](https://forum.inductiveautomation.com/u/Andrew_Taylor)\
**Post date:** [December 22, 2023, 10:49am UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-help/83453/5 "2023-12-22T10:49:50Z")

</div>

Yep, my bad, burning too much midnight oil. Had got the count from another forum post which I thought was similar but clearly not similar enough.

---

<div class="post-metadata">

**Author:** ![franklepkowski](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/franklepkowski/32/4808_2.png) [@franklepkowski](https://forum.inductiveautomation.com/u/franklepkowski)\
**Post date:** [December 22, 2023, 1:07pm UTC](https://forum.inductiveautomation.com/t/ignition-report-sql-query-help/83453/6 "2023-12-22T13:07:19Z")

</div>

You will miss events either way if comms are interrupted between Ignition and the plc. You are correct, best solution is to queue these events in the plc and then utilize a handshake from ignition to remove them from queue.

Frank