﻿ AdventureWorks: Obtain the salary percentile of each employee for a department

# AdventureWorks Database: Obtain the salary percentile of each employee for a department

## SQL Query - AdventureWorks: Exercise-112 with Solution

112. From the following table write a query in SQL to compute the salary percentile for each employee within a given department. Return Department, LastName, Rate, CumeDist, and percentile rank. Sort the result set in ascending order on department and descending order on rate.

N.B.: The cumulative distribution calculates the relative position of a specified value in a group of values.

Sample table: HumanResources.vEmployeeDepartmentHistory

Click to view Full table

Sample Solution:

``````SELECT Department, LastName, Rate,
CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist,
PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank
FROM HumanResources.vEmployeeDepartmentHistory AS edh
INNER JOIN HumanResources.EmployeePayHistory AS e
WHERE Department IN ('Information Services','Document Control')
ORDER BY Department, Rate DESC;
``````

Sample Output:

```department          |lastname     |rate   |cumedist|pctrank           |
--------------------+-------------+-------+--------+------------------+
Document Control    |Arifin       |17.7885|     1.0|               1.0|
Document Control    |Kharatishvili|16.8269|     0.8|               0.5|
Document Control    |Norred       |16.8269|     0.8|               0.5|
Document Control    |Chai         |  10.25|     0.4|               0.0|
Document Control    |Berge        |  10.25|     0.4|               0.0|
Information Services|Trenary      |50.4808|     1.0|               1.0|
Information Services|Conroy       |39.6635|     0.9|0.8888888888888888|
Information Services|Ajenstat     |38.4615|     0.8|0.6666666666666666|
Information Services|Wilson       |38.4615|     0.8|0.6666666666666666|
Information Services|Sharma       |32.4519|     0.6|0.4444444444444444|
Information Services|Connelly     |32.4519|     0.6|0.4444444444444444|
Information Services|Bueno        |27.4038|     0.4|               0.0|
Information Services|Meyyappan    |27.4038|     0.4|               0.0|
Information Services|Bacon        |27.4038|     0.4|               0.0|
Information Services|Berg         |27.4038|     0.4|               0.0|
```

## Practice Online

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

How to avoid the "divide by zero" error in SQL?

```Select Case when divisor=0 then null
Else dividend / divisor
End ,,,
```

OR:

```Select dividend / NULLIF(divisor, 0) ...
```

Ref: https://bit.ly/3dLj7gS

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook