# Sql pareto for column values per row help request

**URL:** <https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119>\
**Category:** Ignition\
**Created:** [May 2, 2022, 2:51pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119 "2022-05-02T14:51:11Z")\
**Posts on this page:** 20\
**Page:** 4

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 5, 2022, 2:02pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/61 "2022-05-05T14:02:04Z")

</div>

append did not work

I posted how extend and append didn’t work. Never found out why.

No error, just never completed. It would just timeout instead of append or extend.  
When I got insert to work, I focused on that.

---

<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:** [May 5, 2022, 2:07pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/62 "2022-05-05T14:07:29Z")

</div>

Probably because you’re inserting on the wrong thing, which might explain why `append` did not work.

Can you try a simple summary of what you need, with a simple example ?

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 5, 2022, 2:07pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/63 "2022-05-05T14:07:53Z")

</div>

What do you mean I am inserting in the wrong thing?  
I always insert on a recalculated length. It is always inserted at the end.

It is working

back before I had it working:  
[this post](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/39) I had appending and extending timing out

I currently do this:

```auto
for row in dataIn:
	data=[row[colName] for colName in reasonHeaders]	
	pareto = [[x, y] for y, x in sorted(zip(data, reasonHeaders),reverse=True)]

print pareto
 
for row in dataIn:
	data2=[row[colName] for colName in suffixHeaders]	
	testThis =[[x,y] for y, x in zip(data2,suffixHeaders)]
	
print testThis
print len(pareto)

for i in range(len(pareto)):
 for j in range(len(testThis)):
  	pareto[i].insert(len(pareto),testThis[j][1])

for i in range(len(pareto)):
	if pareto[i][1]!=0 and pareto[i][3] !=0:  
	   pareto[i].insert(len(pareto),(str(round( (float(pareto[i][1])/float(pareto[i][3]))*100,2) )+"%"))
	else:    
	   pareto[i].insert(len(pareto),str(round(float(0.00),2))+"%") 
	
print '-----'

print pareto[0][0]
print pareto[0][1]
print pareto[0][3]
print pareto[0][4]
	
print pareto  

```

The results are great.

It sounds like you are saying it is efficient to calculate the length once.  
If I counted the length only once, then when I insert, the next insert would be off by one.

---

<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:** [May 5, 2022, 2:15pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/64 "2022-05-05T14:15:34Z")

</div>

I mean you're inserting in `pareto[i]` using the length of `pareto`, which doesn't look like something you'd want to do.  
I kind of feel like it working is a bug 😃

> [@zacharyw.larson](#):
>
> ```auto
> for row in pareto:
> for col in pareto:
> pareto.append(testThis)
> 
> ```

This is appending the same thing `testThis` to the same receiver `pareto` over and over again.  
Note that there, you were appending to `pareto`, not to `pareto[i]`

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 5, 2022, 2:17pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/65 "2022-05-05T14:17:44Z")

</div>

how is it a bug?

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 5, 2022, 2:18pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/66 "2022-05-05T14:18:51Z")

</div>

append doesn’t work

it times out

---

<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:** [May 5, 2022, 2:24pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/67 "2022-05-05T14:24:15Z")

</div>

> [@zacharyw.larson](#):
>
> If I counted the length only once, then when I insert, the next insert would be off by one.

Why ? When does the length of `pareto` change ? You're inserting into `pareto[i]`, that doesn't change the length of `pareto` !

look:

```auto
pareto = [[], [], []]

print(len(pareto))

for i, p in enumerate(pareto):
	p.insert(len(pareto), i)
	print(len(pareto))

print(pareto)

```

output:

> 3  
> 3  
> 3  
> 3  
> [[0], [1], [2]]

Clearly, inserting at index 3 in an empty list works, but it's not what you want to do.  
It's a bug in the logic. It doesn't prevent it from having a valid output, but it's not what you want to do.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 5, 2022, 2:27pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/68 "2022-05-05T14:27:15Z")

</div>

I don’t understand that.

I think you are saying something important, but I do not understand what you are saying.

I think I need a break, and will come back refreshed maybe.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [May 5, 2022, 2:30pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/69 "2022-05-05T14:30:40Z")

</div>

He is saying that if you append to a list inside of pareto, you are not changing the length of pareto but the length of the list inside pareto. If pareto[1] has 50 items in it, pareto still only has 3 lists in it.

---

<div class="post-metadata">

**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:** [May 5, 2022, 2:31pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/70 "2022-05-05T14:31:27Z")

</div>

To be even more exhaustive:

```python
pareto = [[],[],[]]

print len(pareto)

for i in range(len(pareto)):
	pareto[i].insert(len(pareto), i)
	print len(pareto)

print pareto

for i in range(len(pareto)):
	pareto[i].append(i)
	print len(pareto)

print pareto

for i,p in enumerate(pareto):
	p.append(i)
	print len(pareto)

print pareto

```

output:

> 3  
> 3  
> 3  
> 3  
> [[0], [1], [2]]  
> 3  
> 3  
> 3  
> [[0, 0], [1, 1], [2, 2]]  
> 3  
> 3  
> 3  
> [[0, 0, 0], [1, 1, 1], [2, 2, 2]]

appending is the exact same thing as "inserting at the end"

---

<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:** [May 5, 2022, 2:35pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/71 "2022-05-05T14:35:18Z")

</div>

I’m also saying that the current code will break sooner or later.  
Probably sooner.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 5, 2022, 4:21pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/72 "2022-05-05T16:21:29Z")

</div>

> [@zacharyw.larson](#):
>
> ```auto
> for i in range(len(pareto)):
> for j in range(len(testThis)):
> pareto[i].insert(len(pareto),testThis[j][1])
> 
> ```

I am back

haha super bugged code that works and doesn't

so len(pareto) is 18

I pass 1-18 through "i"

len(testThis) is 2

len(pareto[i]) is 2 before I start to add things to it, gets to be size 5 at the end right?

so where I am calling len(pareto) what is happening?  
I am inserting at the 18th position which doesn't exist so it goes on the end?

I should be calling len(pareto[i]) to get the real position to insert if I understand it.

---

<div class="post-metadata">

**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\
**Post date:** [May 5, 2022, 4:39pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/73 "2022-05-05T16:39:46Z")

</div>

> [@zacharyw.larson](#):
>
> so len(pareto) is 18
> 
> I pass 1-18 through "i"

You mean you pass 0-17?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [May 5, 2022, 5:00pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/74 "2022-05-05T17:00:11Z")

</div>

Python has 0-based indexing, so the first element is 0…17. Some software has 1-based indexing, so for Matlab, the first element starts with 1. As mentioned, use `.append()` not insert. If append doesn’t work you have other problems. `insert()` will work but not the best solution to this.

It should probably be this, but I still say append should be used instead.

```auto
pareto[i].insert(len(pareto[i]),testThis[j][1])

```

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 5, 2022, 5:01pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/75 "2022-05-05T17:01:11Z")

</div>

why do you recommend append over insert?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [May 5, 2022, 5:01pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/76 "2022-05-05T17:01:38Z")

</div>

it will automatically put the new element at the end of the list

When you start getting into programming/scripting that is hard to follow, I recommend writing pseudo code/algorithm first (detailed explanation of each logical step), then use that as a guide to write the script.

---

<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:** [May 5, 2022, 5:18pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/77 "2022-05-05T17:18:56Z")

</div>

Let’s take this from the top.

What you have:  
A database table with a bunch of columns, 4 of those being metadata

What you want:  
A table with, with rows matching the database, but with only 5 columns, for the highest 5 columns from the database table

What I’m not sure about:  
What happens to the 4 metadata columns ?  
What headers do you want ? If you want to keep the headers of the 5 selected columns, how should they be stored, considering they’re not going to be the same for each row ?

Anything I missed ?

Provide that information and I’ll write the script you want, with explanation about everything.

---

<div class="post-metadata">

**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:** [May 5, 2022, 5:20pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/78 "2022-05-05T17:20:38Z")

</div>

> [@pascal.fragnoud](#):
>
> but with only 5 columns, for the highest 5 columns from

for the highest 5 columns from each _row_ of the table.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 5, 2022, 5:22pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/79 "2022-05-05T17:22:02Z")

</div>

Requirements had changed on me as I went.

My outputs should be like:  
machine, reason, qty, net, defects, percent qty/defects for each row  
rows are sorted, not just 5

on top of that, I removed some of the rows entirely because some of the reasons, were kind of in a between meta data and not exactly a reason

it is kind of unfinished as I need more rows, or more pieces of data from the plc  
the total is not 100%

* * *

I am still looking at posts from this morning.

Some of the things suggested seemed like I should implement.

* * *

and now I add the prefixheaders

which is meta data, that goes first, but I probably could append it to the end, then position it in the table component in perspective with columns.

your offer is generous @pascal.fragnoud  
I want to understand the scripting more and I think if I hand it off, then try to learn it afterwards, it will not be effective for 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:** [May 5, 2022, 5:50pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/80 "2022-05-05T17:50:09Z")

</div>

> [@lrose](#):
>
> for the highest 5 columns from each _row_ of the table.

Yes that's how I understood it, this is what 'with rows matching the database' meant.  
For each row in the database table, make a row in a table where the columns are the top 5 database columns for that row.

> [@zacharyw.larson](#):
>
> My outputs should be like:  
> machine, reason, qty, net, defects, percent qty/defects for each row  
> rows are sorted, not just 5
> 
> on top of that, I removed some of the rows entirely because some of the reasons, were kind of in a between meta data and not exactly a reason
> 
> it is kind of unfinished as I need more rows, or more pieces of data from the plc  
> the total is not 100%

I have no idea what this means.

[Previous page](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119.md?page=3)

[Next page](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119.md?page=5)