AdventureWorks Database: Calculate a row number based on SalesYTD ranking
SQL Query - AdventureWorks: Exercise-121 with Solution
121. From the following table write a query in SQL to calculate a row number for the salespeople based on their year-to-date sales ranking. Return row number, first name, last name, and year-to-date sales.
Sample table: Sales.vSalesPerson
businessentityid|title|firstname|middlename|lastname |suffix|jobtitle |phonenumber |phonenumbertype|emailaddress |emailpromotion|addressline1 |addressline2|city |stateprovincename|postalcode|countryregionname|territoryname |territorygroup|salesquota|salesytd |saleslastyear|
----------------+-----+---------+----------+-----------------+------+----------------------------+-------------------+---------------+----------------------------+--------------+--------------------------+------------+-------------+-----------------+----------+-----------------+--------------+--------------+----------+------------+-------------+
274| |Stephen |Y |Jiang | |North American Sales Manager|238-555-0197 |Cell |[email protected]| 0|2427 Notre Dame Ave. | |Redmond |Washington |98052 |United States | | | | 559697.5639| 0|
275| |Michael |G |Blythe | |Sales Representative |257-555-0154 |Cell |[email protected]| 1|8154 Via Mexico | |Detroit |Michigan |48226 |United States |Northeast |North America | 300000|3763178.1787| 1750406.4785|
276| |Linda |C |Mitchell | |Sales Representative |883-555-0116 |Work |[email protected] | 0|2487 Riverside Drive | |Nevada |Utah |84407 |United States |Southwest |North America | 250000|4251368.5497| 1439156.0291|
277| |Jillian | |Carson | |Sales Representative |517-555-0117 |Work |[email protected]| 1|80 Sunview Terrace | |Duluth |Minnesota |55802 |United States |Central |North America | 250000|3189418.3662| 1997186.2037|
278| |Garrett |R |Vargas | |Sales Representative |922-555-0165 |Work |[email protected]| 0|10203 Acorn Avenue | |Calgary |Alberta |T2P 2G8 |Canada |Canada |North America | 250000|1453719.4653| 1620276.8966|
279| |Tsvi |Michael |Reiter | |Sales Representative |664-555-0112 |Work |[email protected] | 1|8291 Crossbow Way | |Memphis |Tennessee |38103 |United States |Southeast |North America | 300000| 2315185.611| 1849640.9418|
280| |Pamela |O |Ansman-Wolfe | |Sales Representative |340-555-0193 |Cell |[email protected] | 1|636 Vine Hill Way | |Portland |Oregon |97205 |United States |Northwest |North America | 250000|1352577.1325| 1927059.178|
281| |Shu |K |Ito | |Sales Representative |330-555-0120 |Cell |[email protected] | 2|5725 Glaze Drive | |San Francisco|California |94109 |United States |Southwest |North America | 250000|2458535.6169| 2073505.9999|
282| |José |Edvaldo |Saraiva | |Sales Representative |185-555-0169 |Work |josé[email protected] | 0|9100 Sheppard Avenue North| |Ottawa |Ontario |K4B 1T7 |Canada |Canada |North America | 250000|2604540.7172| 2038234.6549|
283| |David |R |Campbell | |Sales Representative |740-555-0182 |Work |[email protected] | 0|2284 Azalea Avenue | |Bellevue |Washington |98004 |United States |Northwest |North America | 250000|1573012.9383| 1371635.3158|
284|Mr. |Tete |A |Mensa-Annan | |Sales Representative |615-555-0153 |Work |[email protected] | 1|3997 Via De Luna | |Cambridge |Massachusetts |02139 |United States |Northwest |North America | 300000|1576562.1966| 0|
285|Mr. |Syed |E |Abbas | |Pacific Sales Manager |926-555-0182 |Work |[email protected] | 0|7484 Roundtree Drive | |Bothell |Washington |98011 |United States | | | | 172524.4512| 0|
286| |Lynn |N |Tsoflias | |Sales Representative |1 (11) 500 555-0190|Cell |[email protected] | 2|34 Waterloo Road | |Melbourne |Victoria |3000 |Australia |Australia |Pacific | 250000|1421810.9242| 2278548.9776|
287| |Amy |E |Alberts | |European Sales Manager |775-555-0164 |Work |[email protected] | 1|5009 Orange Street | |Renton |Washington |98055 |United States | | | | 519905.932| 0|
288| |Rachel |B |Valdez | |Sales Representative |1 (11) 500 555-0140|Cell |[email protected] | 0|Pascalstr 951 | |Berlin |Hamburg |14111 |Germany |Germany |Europe | 250000|1827066.7118| 1307949.7917|
289| |Jae |B |Pak | |Sales Representative |1 (11) 500 555-0145|Work |[email protected] | 0|Downshire Way | |Cambridge |England |BA5 3HX |United Kingdom |United Kingdom|Europe | 250000|4116871.2277| 1635823.3967|
290| |Ranjit |R |Varkey Chudukatil| |Sales Representative |1 (11) 500 555-0117|Cell |[email protected] | 0|94, rue Descartes | |Bordeaux |Gironde |33000 |France |France |Europe | 250000|3121616.3202| 2396539.7601|
Sample Solution:
-- Selecting specific columns and calculating row numbers based on SalesYTD
SELECT
-- Calculating row numbers based on SalesYTD in descending order
ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,
-- Selecting the FirstName column
FirstName,
-- Selecting the LastName column
LastName,
-- Rounding SalesYTD to 2 decimal places and aliasing it as "Sales YTD"
ROUND(SalesYTD, 2) AS "Sales YTD"
-- Selecting data from the vSalesPerson view
FROM
Sales.vSalesPerson
-- Filtering records where TerritoryName is not NULL and SalesYTD is not equal to 0
WHERE
TerritoryName IS NOT NULL
AND SalesYTD <> 0;
Explanation:
- This SQL code selects specific columns from the vSalesPerson view.
- It calculates row numbers based on SalesYTD in descending order, indicating the ranking of each row based on SalesYTD.
- The ROW_NUMBER() function assigns a unique sequential integer to each row in the result set, ordered by SalesYTD in descending order.
- The result set includes columns for row numbers (aliased as "Row"), first names (FirstName), last names (LastName), and rounded SalesYTD values (aliased as "Sales YTD").
- Records are filtered to include only those where TerritoryName is not NULL and SalesYTD is not equal to 0.
- The result set will contain the calculated row numbers along with the corresponding first names, last names, and Sales YTD values for salespersons meeting the specified conditions.
Sample Output:
row|firstname|lastname |Sales YTD | ---+---------+-----------------+----------+ 1|Linda |Mitchell |4251368.55| 2|Jae |Pak |4116871.23| 3|Michael |Blythe |3763178.18| 4|Jillian |Carson |3189418.37| 5|Ranjit |Varkey Chudukatil|3121616.32| 6|José |Saraiva |2604540.72| 7|Shu |Ito |2458535.62| 8|Tsvi |Reiter |2315185.61| 9|Rachel |Valdez |1827066.71| 10|Tete |Mensa-Annan |1576562.20| 11|David |Campbell |1573012.94| 12|Garrett |Vargas |1453719.47| 13|Lynn |Tsoflias |1421810.92| 14|Pamela |Ansman-Wolfe |1352577.13|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Find the salary of top ten employees.
Next: Calculate row numbers for rows between 50 to 60 inclusive.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-121.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics