CODEWITHSUNDEEP
sql-servert-sqlsql-server-2016
User20391823
User20391823

Reputation: 15

Using Pivot function in conjunction with Sum function in TSQL

I am trying to create a query from this table: above is my table, and below is what I need my query to show.

What I've tried: first, I tried using UNIONs, but it did not allow me to sum or average. Then I tried to experiment with the PIVOT function, but again, the issue is the percent requirement. One thing that I did not want to do is to create a table and then storing the information there. I think that this can be resolved by a query, so that is not a desirable solution.  % of total is calculated by getting total orders for the Product Category. Which in the example is 001, and then dividing total order for the subcate region over the total. Many, many thanks in advance!

query and resultset

Upvotes: 1

Views: 51

Answers (1)

Rob Farley
Rob Farley

Reputation: 15849

The kind of thing you want is more like UNPIVOT functionality rather than PIVOT functionality, but I prefer to use APPLY for that. Let's start by doing that and then move onto the percentage (for which will use the OVER clause).

SELECT t.ProductCategory, t.ProductSubCategory, u.Region, u.[Total Orders]
FROM dbo.OriginalTable t
CROSS APPLY
    (SELECT 'North America' as Region, [Total Orders Placed in North America] as [Total Orders]
     UNION ALL
     SELECT 'South America' as Region, [Total Orders Placed in South America] as [Total Orders]
     UNION ALL
     SELECT 'North Africa' as Region, [Total Orders Placed in North Africa] as [Total Orders]
     UNION ALL
     SELECT 'South Africa' as Region, [Total Orders Placed in South Africa] as [Total Orders]
    ) u
;

Right, so now let's sort out that percentage.

We want to consider the sum of the orders per ProductCategory. To do this and still having access to the other columns, we use the OVER clause with 'PARTITION BY', like this (rather than a sub-query with GROUP BY, which would stop us from hooking back into u.[Total Orders]):

SUM(u.[Total Orders]) OVER (PARTITION BY p.ProductCategory)

It's that easy. So then by dividing u.[Total Orders] by that, we get what we want. This leaves it as a portion of 1 (ie, 1.5% will be 0.015), but you can handle how it displays in whatever consumes your final query.

SELECT t.ProductCategory, t.ProductSubCategory, u.Region, u.[Total Orders],
    u.[Total Orders] / SUM(u.[Total Orders]) OVER (PARTITION BY t.ProductCategory) as [Percentage of Total]
FROM dbo.OriginalTable t
CROSS APPLY
    (SELECT 'North America' as Region, [Total Orders Placed in North America] as TotalOrders
     UNION ALL
     SELECT 'South America' as Region, [Total Orders Placed in South America] as [Total Orders]
     UNION ALL
     SELECT 'North Africa' as Region, [Total Orders Placed in North Africa] as [Total Orders]
     UNION ALL
     SELECT 'South Africa' as Region, [Total Orders Placed in South Africa] as [Total Orders]
    ) u
;

Upvotes: 1

Related Questions