AdventureWorks Database: Calculate a row number based on SalesYTD ranking
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.