w3resource

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.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.