CODEWITHSUNDEEP
sql-serveradventureworks
porsh01
porsh01

Reputation: 95

Based on territory, get the top customers and their total sales in percentage

Using adventureworks database.

I am stuck with top 5 customers based on the territory. How can we get the output for this in the given format?

 SELECT TOP 5 CustomerID
    ,oh.TerritoryID
    ,Name
    ,SUM(TotalDue) / (
        SELECT SUM(TotalDue)
        FROM Sales.SalesOrderHeader
        ) * 100 AS [%_of_TotalSale]
FROM Sales.SalesOrderHeader oh
INNER JOIN Sales.SalesTerritory st ON st.TerritoryID = oh.TerritoryID
GROUP BY oh.TerritoryID
    ,CustomerID
    ,Name
ORDER BY [%_of_TotalSale] DESC;

My output

My output

The output should look like this

The output should look like this

Upvotes: 3

Views: 1103

Answers (1)

gotqn
gotqn

Reputation: 43636

Your query have some issues. You need to calculated the total per territory - not the whole total.

Note, the code below can be separated to individual statements. Also, there are other solutions of this task. 

WITH DataSource AS
(
    SELECT DISTINCT TerritoryID
                    ,CustomerID
                    ,SUM(TotalDue) OVER (PARTITION BY TerritoryID,CustomerID) * 100 / SUM(TotalDue) OVER (PARTITION BY TerritoryID) AS [%_of_TotalSale]
    FROM Sales.SalesOrderHeader
), DataSourceUsersRanked AS
(
    SELECT *
          ,ROW_NUMBER() OVER (PARTITION BY TerritoryID ORDER BY [%_of_TotalSale] DESC) AS RN
    FROM DataSource
), DataSourceUsersFiletred AS
(
    SELECT *
    FROM DataSourceUsersRanked
    WHERE RN <= 5
)
SELECT DSF.TerritoryID
      ,st.[Name]
      ,SUM([%_of_TotalSale]) AS [%_of_TotalSale]
      ,MAX(UserIDs) AS [Top5Customers]
FROM DataSourceUsersFiletred DSF
INNER JOIN Sales.SalesTerritory st
    ON DSF.TerritoryID = st.TerritoryID
CROSS APPLY
(
    SELECT STUFF
    (
        (
            SELECT ',' + CAST(CustomerID AS VARCHAR(12))
            FROM DataSourceUsersFiletred DS1
            WHERE DS1.[TerritoryID] = DSF.[TerritoryID]
            ORDER BY CustomerID
            FOR XML PATH(''), TYPE          
        ).value('.', 'VARCHAR(MAX)')
        ,1
        ,1
        ,''
    )
) Users(UserIDs)
GROUP BY DSF.TerritoryID
        ,st.[Name]
ORDER BY TerritoryID;

enter image description here

Upvotes: 2

Related Questions