| |
SQL
Server 2005
does not support multiple parameters in
user made aggregate functions. So, for each
percentile calculation from 1 to 99 we have
created a seperate function. For instance,
for a table "tmp" with two fields
"a" and "b" in order
to calculate 6th, 50th, 90th ,95th percentile
and median of field a by field b, following
queries will get the required results.
Select
b,dbo.percentile06(a) from
tmp group by
b --6th Percentile
Select b,dbo.percentile50(a)
from tmp group
by b --50th
Percentile
Select b,dbo.percentile90(a)
from tmp group
by b --90th
Percentile
Select b,dbo.percentile95(a)
from tmp group
by b --95th
Percentile Select
b,dbo.median(a) from
tmp group by b
-- Median
Try it Now:(works on maximum 100 records
per group)
|
|
Full Version:
|
|
| |
SQL
Server 2008
does support multiple parameters in user
made aggregate functions, so the syntax
is relatively simple here. It works exaclty
the same as MS Excel the only difference
is that instead of selecing a range you
use a field name in the function. For instance,
Select
b,dbo.Percentile(a,.90) [90th Percentile]
from tmp group
by b --90th
Percentile
Select b,dbo.Percentile(a,.95)
[95th Percentile] from
tmp group by b
--95th Percentile
Select b,dbo.Median(a)
[Median] from
tmp group by b
-- Median
Try it Now:(works on maximum 100 records
per group)
|
|
Full Version:
|
|
