PERCENTILE_CONT() function syntax with an example in SQL Server
Introduction:
In my previous posts we have seen what is Transact-Sql in sql server and What are new top features of T-Sql in server. This is one of the best sql server interview Questions and answers. Watch more ASP.NET, C#.NET, Jquery, Sql Server, HTML5, HTML, CSS, JQUERY UI Interview questions from here.
Description:
·This function calculates a percentile based on a continuous
distribution of the column value.
Example:
The following example uses PERCENTILE_CONT and PERCENTILE_DISC to
find the median employee salary in each department.
USE AdventureWorks2008R2;
SELECT DISTINCT Name AS
DepartmentName,
PERCENTILE_CONT (0.5) WITHIN
GROUP (ORDER BY ph.Rate)
OVER (PARTITION BY
Name) AS MedianCont,
PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY ph.Rate)
OVER (PARTITION BY
Name) AS MedianDisc
FROM HumanResources.Department AS d
INNER JOIN
HumanResources.EmployeeDepartmentHistory AS dh
ON
dh.DepartmentID = d.DepartmentID
INNER JOIN
HumanResources.EmployeePayHistory AS ph
ON
ph.BusinessEntityID = dh.BusinessEntityID
WHERE dh.EndDate IS NULL
And Name in ('Document Control', 'Engineering', 'Executive', 'Human
Resources')
Result:
DepartmentName
|
Rate
|
Cdist
|
MedianCont
|
MedianDisc
|
Document Control
|
10.25
|
0.4
|
16.8269
|
16.8269
|
Document Control
|
16.8269
|
0.8
|
16.8269
|
16.8269
|
Document Control
|
17.7885
|
1
|
16.8269
|
16.8269
|
Engineering
|
32.6923
|
0.5
|
34.375
|
32.6923
|
Engineering
|
36.0577
|
0.666666666666667
|
34.375
|
32.6923
|
Engineering
|
43.2692
|
0.833333333333333
|
34.375
|
32.6923
|
Engineering
|
63.4615
|
1
|
34.375
|
32.6923
|
Executive
|
39.06
|
0.25
|
54.32695
|
48.5577
|
Executive
|
48.5577
|
0.5
|
54.32695
|
48.5577
|
Executive
|
60.0962
|
0.75
|
54.32695
|
48.5577
|
Executive
|
125.50
|
1
|
54.32695
|
48.5577
|
Human Resources
|
13.9423
|
0.333333333333333
|
17.42785
|
16.5865
|
Human Resources
|
16.5865
|
0.5
|
17.42785
|
16.5865
|
Human Resources
|
18.2692
|
0.833333333333333
|
17.42785
|
16.5865
|
Human Resources
|
27.1394
|
1
|
17.42785
|
16.5865
|
If you have any queries or suggestions, please feel free to ask in comments section.
Post a Comment
Please give your valuable feedback on this post. You can submit any ASP.NET article here. We will post that article in this website by your name.