@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.
which reads: 43 results with value 5, 2 results with value 2, 5 results with value 4, etc.
The database is ms-access :) .mdb
It's Split in MS Access if I'm not mistaken
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.
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)
fuckwit135738dOnly two hours execution time? Damn you're lucky. One of my queries runs for 3 1/2 days straight. Oh and it's unkillable.