AdventureWorks Database: Divide rows into defined groups based on SalesYTD

SQL Query - AdventureWorks: Exercise-118 with Solution

118. From the following table write a query in SQL to divide rows into four groups of employees based on their year-to-date sales. Return first name, last name, group as quartile, year-to-date sales, and postal code.

Sample table: Sales.SalesPerson

Click to view Full table

Sample table: Person.Person

Click to view Full table

Sample table: Person.Address

Click to view Full table

Sample Solution:

SELECT p.FirstName, p.LastName  
    ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile  
    ,CAST(SalesYTD as VARCHAR(20) ) AS SalesYTD  
    , a.PostalCode  
FROM Sales.SalesPerson AS s   
INNER JOIN Person.Person AS p   
    ON s.BusinessEntityID = p.BusinessEntityID  
INNER JOIN Person.Address AS a   
    ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL   
    AND SalesYTD <> 0; 

Sample Output:

firstname|lastname         |quartile|salesytd    |postalcode|
---------+-----------------+--------+------------+----------+
Linda    |Mitchell         |       1|4251368.5497|98027     |
Jae      |Pak              |       1|4116871.2277|98055     |
Michael  |Blythe           |       1|3763178.1787|98027     |
Jillian  |Carson           |       1|3189418.3662|98027     |
Ranjit   |Varkey Chudukatil|       2|3121616.3202|98055     |
José     |Saraiva          |       2|2604540.7172|98055     |
Shu      |Ito              |       2|2458535.6169|98055     |
Tsvi     |Reiter           |       2|2315185.611 |98027     |
Rachel   |Valdez           |       3|1827066.7118|98055     |
Tete     |Mensa-Annan      |       3|1576562.1966|98055     |
David    |Campbell         |       3|1573012.9383|98055     |
Garrett  |Vargas           |       4|1453719.4653|98027     |
Lynn     |Tsoflias         |       4|1421810.9242|98055     |
Pamela   |Ansman-Wolfe     |       4|1352577.1325|98027     |

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Return the top ten employees ranked by their salary.
Next: Rank the products in inventory according to their quantities.