Sum on a Pivot Table

Question

Sum on a Pivot Table

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

I'm learning how to use tables with PIVOT in Sql-Server. I have a table with the item name, the vendor name, and the customer id.

Item | Customer | ID Rep Pen Set | 1001 | Richard Binder | 25092 | Nick Pen Set | 5149 | Morgan Binder | 5453 | Susan Pen Set | 55456 | Matthew Pencil | 5470 | Richard Desk | 481 | James

I created PIVOT to show the amount of items that each seller sold, and my code stayed like this

select * from(
select 
        [Item]
        ,[Customer ID]
        ,Rep
from
        [dbo].[WRK_Transacoes] 
) as TabUm 
Pivot (
    count([Customer ID])
    FOR [Item]
    IN ([Binder]
        ,[Desk]
        ,[Pen Set]
        ,[Pen]
        ,[Pencil])
)as PivotTable

Now, I wish you had a column showing the total of the items that each vendor sold. How do I do that? Below is how I want my table Vendedor|Binder|Desk|Pen|Set Pen|Pencil| Total Alex 2 0 1 0 2 5 Bill 2 0 0 1 2 5 James 1 1 0 1 1 4 Matthew 1 1 2 0 0 4 Morgan 1 0 1 0 1 3 Nick 1 0 0 1 0 2 Rachel 1 0 0 0 3 4 Richard 3 0 2 1 2 8 Smith 1 1 0 0 1 3 Susan 1 0 1 1 0 3

Sorry for the size of the question.

sql sql-server pivot

asked by anonymous 30.04.2018 / 06:57

2 answers

0

Here is a solution that uses the classic pivot, through the GROUP BY clause:

-- código #1
SELECT [ID Rep], 
       sum(case when Item = 'Binder' then 1 else 0 end) as Binder,
       sum(case when Item = 'Desk' then 1 else 0 end) as Desk,
       sum(case when Item = 'Pen Set' then 1 else 0 end) as [Pen Set],
       sum(case when Item = 'Pen' then 1 else 0 end) as Pen,
       sum(case when Item = 'Pencil' then 1 else 0 end) as Pencil, 
       count(*) as Total
  from dbo.WRK_Transacoes
  where Item in ('Binder', 'Desk', 'Pen Set', 'Pen', 'Pencil')
  group by [ID Rep];

30.04.2018 / 18:33

Concatenate CPF in Mysql Date format error when it is Brazilian Daylight Saving Day (BRST)

score 2 · Accepted Answer

One solution is to calculate the total number of items each seller sold before performing the PIVOT operation. One possible way is through the COUNT function (used as a window function).

Here's the statement and link to SQLFiddle .

SELECT  [ID Rep]
       ,[Binder]
       ,[Desk]
       ,[Pen]
       ,[Pen Set]
       ,[Pencil]
       ,[TotalRep]
  FROM 
  (
       SELECT  [Item]
              ,[Customer ID]
              ,[ID Rep]
              ,COUNT([Item]) OVER (PARTITION BY [ID Rep]) AS TotalRep
         FROM [dbo].[WRK_Transacoes] 
   ) AS TabUm 
PIVOT 
(
    COUNT([Customer ID])
    FOR [Item] IN (
                    [Binder]
                   ,[Desk]
                   ,[Pen Set]
                   ,[Pen]
                   ,[Pencil])
) AS PivotTable