w3resource

AdventureWorks Database: Retrieve the salesperson for each PostalCode

SQL Query - AdventureWorks: Exercise-21 with Solution

21. From the following tables write a query in SQL to retrieve the salesperson for each PostalCode who belongs to a territory and SalesYTD is not zero. Return row numbers of each group of PostalCode, last name, salesytd, postalcode column. Sort the salesytd of each postalcode group in descending order. Shorts the postalcode in ascending order.

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 ROW_NUMBER() OVER win AS "Row Number",
    pp.LastName, sp.SalesYTD, pa.PostalCode
FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS pp
        ON sp.BusinessEntityID = pp.BusinessEntityID
    INNER JOIN Person.Address AS pa
        ON pa.AddressID = pp.BusinessEntityID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0
WINDOW win AS (PARTITION BY PostalCode ORDER BY SalesYTD DESC)
ORDER BY PostalCode;

or

SELECT ROW_NUMBER() OVER (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",
pp.LastName, sp.SalesYTD, pa.PostalCode
FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS pp
        ON sp.BusinessEntityID = pp.BusinessEntityID
    INNER JOIN Person.Address AS pa
        ON pa.AddressID = pp.BusinessEntityID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0
ORDER BY PostalCode;

Sample Output:

Row Number|lastname         |salesytd    |postalcode|
----------+-----------------+------------+----------+
         1|Mitchell         |4251368.5497|98027     |
         2|Blythe           |3763178.1787|98027     |
         3|Carson           |3189418.3662|98027     |
         4|Reiter           | 2315185.611|98027     |
         5|Vargas           |1453719.4653|98027     |
         6|Ansman-Wolfe     |1352577.1325|98027     |
         1|Pak              |4116871.2277|98055     |
         2|Varkey Chudukatil|3121616.3202|98055     |
         3|Saraiva          |2604540.7172|98055     |
         4|Ito              |2458535.6169|98055     |
         5|Valdez           |1827066.7118|98055     |
         6|Mensa-Annan      |1576562.1966|98055     |
         7|Campbell         |1573012.9383|98055     |
         8|Tsoflias         |1421810.9242|98055     |

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: List contacts who are Purchasing Manager.
Next: Number of contacts for each type and name.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

T-SQL Cast versus Convert?

CONVERT is SQL Server specific, CAST is ANSI.

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.

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