# Loop through dataset and add new column in same dataset that list how many time an item occured in each loop

**URL:** <https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183>\
**Category:** Ignition\
**Created:** [November 27, 2024, 2:28pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183 "2024-11-27T14:28:04Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![rpaisley](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@rpaisley](https://forum.inductiveautomation.com/u/rpaisley)\
**Post date:** [November 27, 2024, 2:28pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/1 "2024-11-27T14:28:04Z")

</div>

Hi I am trying to loop through a dataset from an SQL query giving me new column with # of previous occurances of an item in another column.

is this able to do?

---

<div class="post-metadata">

**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\
**Post date:** [November 27, 2024, 2:32pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/2 "2024-11-27T14:32:25Z")

</div>

most databases have `count()` function

---

<div class="post-metadata">

**Author:** ![rpaisley](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@rpaisley](https://forum.inductiveautomation.com/u/rpaisley)\
**Post date:** [November 27, 2024, 2:33pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/3 "2024-11-27T14:33:27Z")

</div>

I am just not sure how to set it up never looped through yet.

---

<div class="post-metadata">

**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\
**Post date:** [November 27, 2024, 2:34pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/4 "2024-11-27T14:34:58Z")

</div>

you should probably do this through the query, not through the dataset

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 27, 2024, 2:35pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/5 "2024-11-27T14:35:47Z")

</div>

Show us your SQL query. Please see [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831).

---

<div class="post-metadata">

**Author:** ![rpaisley](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@rpaisley](https://forum.inductiveautomation.com/u/rpaisley)\
**Post date:** [November 27, 2024, 2:37pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/6 "2024-11-27T14:37:37Z")

</div>

```python
select CONCAT((CONVERT(INT,[Requested Ship Date])+2),[SOP Number], [Customer PO],[Item])as Column1,[Requested Ship Date],[SOP Number], [Customer PO], [Customer],[CUSTITEMNMBR], [Item], [Qty Remaining to Ship],[Qty On Hand]
from [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr]
order by [Requested Ship Date] ASC

```

I am running a view in SQL.

---

<div class="post-metadata">

**Author:** ![rpaisley](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@rpaisley](https://forum.inductiveautomation.com/u/rpaisley)\
**Post date:** [November 27, 2024, 2:39pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/7 "2024-11-27T14:39:38Z")

</div>

I want to count the number of times [Item] is above in the data giving # of previous occurances of that item number.

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

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 27, 2024, 2:49pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/8 "2024-11-27T14:49:55Z")

</div>

You should be able to do this in SQL. Try this. (It should be close to working!)

```python
SELECT 
    CONCAT(
        (CONVERT(INT,[Requested Ship Date])+2),
        [SOP Number], 
        [Customer PO],
        [Item]
    ) AS Column1,
    [Requested Ship Date],
    [SOP Number], 
    [Customer PO], 
    [Customer],
    [CUSTITEMNMBR], 
    [Item], 
    [Qty Remaining to Ship],
    [Qty On Hand],
    (SELECT 
        COUNT(Item)
        FROM [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr] t2
        WHERE t1.Item = t2.Item
          AND t1.Item > t2.Item -- <<<< Error here. See Post #13.
    ) AS PrevOcc
FROM [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr] t1
ORDER BY [Requested Ship Date] ASC

```

Tips:

- Make your SQL readable. Use line breaks. Always add a space after commas (same as writing in English).
- Be consistent with your SQL keyword case - either uppercase or lowercase but don't mix them.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 27, 2024, 2:55pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/9 "2024-11-27T14:55:00Z")

</div>

I would use `count()` as a [Window Aggregate](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16), like so:

```python
SELECT 
    CONCAT(
        (CONVERT(INT,[Requested Ship Date])+2),
         [SOP Number], 
        [Customer PO],
        [Item]
    ) AS Column1,
    [Requested Ship Date],
    [SOP Number], 
    [Customer PO], 
    [Customer],
    [CUSTITEMNMBR], 
    [Item], 
    [Qty Remaining to Ship],
    [Qty On Hand],
    COUNT(*) OVER (PARTITION BY [Item] ORDER BY [Requested Ship Date] UNBOUNDED PRECEDING) AS PrevOcc
FROM [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr] t1
ORDER BY [Requested Ship Date]

```

---

<div class="post-metadata">

**Author:** ![rpaisley](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@rpaisley](https://forum.inductiveautomation.com/u/rpaisley)\
**Post date:** [November 27, 2024, 3:14pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/10 "2024-11-27T15:14:02Z")

</div>

> [@Transistor](#):
>
> ```python
> SELECT 
> CONCAT(
> (CONVERT(INT,[Requested Ship Date])+2),
> [SOP Number], 
> [Customer PO],
> [Item]
> ) AS Column1,
> [Requested Ship Date],
> [SOP Number], 
> [Customer PO], 
> [Customer],
> [CUSTITEMNMBR], 
> [Item], 
> [Qty Remaining to Ship],
> [Qty On Hand],
> (SELECT 
> COUNT(Item)
> FROM [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr] t2
> WHERE t1.Item = t2.Item
> AND t1.Item > t2.Item
> ) AS PrevOcc
> FROM [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr] t1
> ORDER BY [Requested Ship Date] ASC
> 
> ```

Does not do anything in the PrevOcc and as you see first two lines are the same.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/d/ed9faa1663247a419aee9a94caf044f02cf6e12d.png)

---

<div class="post-metadata">

**Author:** ![rpaisley](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@rpaisley](https://forum.inductiveautomation.com/u/rpaisley)\
**Post date:** [November 27, 2024, 3:23pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/11 "2024-11-27T15:23:01Z")

</div>

So as we look at the data in excel want to look at each row or rows above and know if that item has already been called out. So line 1 would be 0 on PrecOcc but line 2 would be 1 time it was above. and if happens again would be 2 or so on throughout the data.

---

<div class="post-metadata">

**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\
**Post date:** [November 27, 2024, 3:27pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/12 "2024-11-27T15:27:40Z")

</div>

> [@Transistor](#):
>
> ```python
> WHERE t1.Item = t2.Item
> AND t1.Item > t2.Item
> 
> ```

well this can never be true

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 27, 2024, 3:29pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/13 "2024-11-27T15:29:13Z")

</div>

OK. There's a problem with my query (which Phil's probably avoided).  
`AND t1.Item > t2.Item` won't work as the Item column does not contain unique values. You need to compare on a column that does and is in order. Usually this is the Id column or equivalent. So it should be,

```python
(SELECT COUNT(Item) 
      FROM custitemnmbr t2
      WHERE t1.Item = t2.Item
        AND t1.Id > t2.Id
     ) AS PrevOcc

```

(or whatever you can use instead of Id).

Have a look at this example created for you:

> **[FREE AI-Enhanced Online SQL Server Compiler - For learning & practice](https://sqlfiddle.com/sql-server/online-compiler?id=1a13d10a-893d-4591-830e-a52105f2a5f2)**
>
> Explore our free online SQL Server editor to practice and execute SQL. AI-enhanced to chat, explain, and generate code. Start coding smarter today!

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 27, 2024, 3:34pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/14 "2024-11-27T15:34:17Z")

</div>

> [@Transistor](#):
>
> which Phil's probably avoided

Yes.

> [@Transistor](#):
>
> You need to compare on a column that does and is in order.

**Must** be the same order as the outer query. The `[Requested Ship Date]`.

Window Aggregates usually blow the doors off of subqueries or joins, performance-wise.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 27, 2024, 3:49pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/15 "2024-11-27T15:49:08Z")

</div>

> [@pturmel](#):
>
> Window Aggregates usually blow the doors off of subqueries or joins, performance-wise.

[![](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/4/e4577dcf735d2de1ed94f7b033ce20a775c9d362.jpeg "You Were Only Supposed To Blow The Bloody Doors Off! - The Italian Job (3/10) Movie CLIP (1969) HD") ](https://www.youtube.com/watch?v=7_PX1cVuaVA)

The Italian Job, 1969.

---

<div class="post-metadata">

**Author:** ![rpaisley](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@rpaisley](https://forum.inductiveautomation.com/u/rpaisley)\
**Post date:** [November 28, 2024, 5:26pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/16 "2024-11-28T17:26:06Z")

</div>

@pturmel I tired your code but got this error

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/e/9ea206afc6acef9b6e03ebdd587c2e78f2a5f50f.png)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 28, 2024, 5:30pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/17 "2024-11-28T17:30:27Z")

</div>

Sorry, should be `ROWS UNBOUNDED PRECEDING`

---

<div class="post-metadata">

**Author:** ![rpaisley](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@rpaisley](https://forum.inductiveautomation.com/u/rpaisley)\
**Post date:** [November 28, 2024, 5:38pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/18 "2024-11-28T17:38:48Z")

</div>

Is there a way to start it at zero instead of one?

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/c/bcb63ff716ec69f8f0c20775b2033ee91b71702e.png)

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 28, 2024, 5:40pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/19 "2024-11-28T17:40:28Z")

</div>

Insert ` - 1` before `AS`

---

<div class="post-metadata">

**Author:** ![rpaisley](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@rpaisley](https://forum.inductiveautomation.com/u/rpaisley)\
**Post date:** [November 28, 2024, 5:43pm UTC](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183/20 "2024-11-28T17:43:37Z")

</div>

Yes sir thats what I did I was a little slow.

```python
SELECT 
    [Requested Ship Date],
    [SOP Number], 
    [Customer PO], 
    [Customer],
    [CUSTITEMNMBR], 
    [Item], 
    [Qty Remaining to Ship],
    [Qty On Hand],
    (COUNT(*) OVER (PARTITION BY [Item] 
ORDER BY [Requested Ship Date] ROWS UNBOUNDED PRECEDING) - 1) AS PrevOcc
FROM [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr] t1
ORDER BY [Requested Ship Date]

```

[Next page](https://forum.inductiveautomation.com/t/loop-through-dataset-and-add-new-column-in-same-dataset-that-list-how-many-time-an-item-occured-in-each-loop/99183.md?page=2)