When is it time to give up....?

Looks weird.

@IntrusionCM which part? 2.5 hours or comments? :D

@netikras MSSQL brackets....

Whenever I see them, I want to turn who ever had that in mind to toast.

@IntrusionCM yeah, they look weird to me too. However, there's not much I can do as tables and columns' names have whitespaces :/ And it's a database created by a proprietary 3rd-party tool, so... no way 'round that

@netikras I know I know... ;) Still. Sad. Confusing.

Btw. What are you trying to achieve xD

@IntrusionCM to get the 90th percentile of the results' table. I guess I need to explode it at first, as results in that table are aggregated, e.g.

id,value,acount
1,5,43
2,2,2
3,4,5
...<~600k rows>...

which reads: 43 results with value 5, 2 results with value 2, 5 results with value 4, etc.

The database is ms-access :) .mdb

@netikras Hum.

That will be no fun.

I'd approach it with a derived table based on STRING_SPLIT.

Might make sense to create a temp table and Insert result set there, first.

It's Split in MS Access if I'm not mistaken

@IntrusionCM thanks, but I don't see what does it have to do with strings

@netikras oh...

I thought (MS guys are excellent in this....) that you had an denormalized table with an "CSV string" as column....

Hence the string functions to build an "normalized" derived table.

Now reread a few times and when I understand it correctly, you need to group by value.

Eg
Value - 2, generate sum of all accounts
Value - 5, generate sum of all accounts
...

Then there would be all accounts per value summed up.

I guess you want the 90th percentile of value...

So it would need to be aggregated again, I guess?

Sum(Value*Account) for a total?

And then calculating the 90 % on the result of Sum(Value*Account)?

@IntrusionCM on the contrary, I think I need to explode the table (to repeat as many times as the account column says) to find that single value that represents the 90th percentile.

It's not `total*90/100`.
It's more like `
select valuez.value, valuez.rownum
from (select value, rownum() as rownum from (select value from event_meter order by value asc)) valuez
where valuez.rownum = (select num from (select (count(value) * 90 / 100) num from event_meter));
`

i.e. a 50th percentile is a median ;) not the average

And now I need to make this work for each group (i.e. using group by)

Only two hours execution time? Damn you're lucky. One of my queries runs for 3 1/2 days straight. Oh and it's unkillable.