Sum in a Pivot Table

Question

I’m learning how to use tables with PIVOT in Sql-Server. I have a table with the item name, seller name and 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 the PIVOT to show the amount of items that each seller sold, and my code was 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

With the following output: Vendedor|Binder|Desk|Pen|Set Pen|Pencil Alex 2 0 1 0 2 Bill 2 0 0 1 2 James 1 1 0 1 1 Matthew 1 1 2 0 0 Morgan 1 0 1 0 1 Nick 1 0 0 1 0 Rachel 1 0 0 0 3 Richard 3 0 2 1 2 Smith 1 1 0 0 1 Susan 1 0 1 1 0

Now, ask her to have a column showing the total of the items that each seller sold. How do I do this? Below the example of 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

Excuse the size of the question.

Answer 1

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

Here is the instruction and link to the 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

Answer 2

This is the solution that employs 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];

This form, classic pivot, and others are described in the article "Alas & Pivots". Step by step explanation.