Learn how to add total rows and columns to a pivot query in 15 minutes. In this SQL tutorial I show you how to add totals to pivot queries, I explain how to add a total row and and a total column. Adding totals to pivot queries in SQL Server Management studio is not my preferred approach but it can be done and it can be as simple as adding rows together. In the video I discuss the different approaches of simply adding column values together in the select within the pivot query or combining aggregate queries together within the derived table input to the pivot query.

If you would like to follow along you can copy and paste the code below:
/* create table and insert data */
-- check if table exists
IF OBJECT_ID(N'dbo.Orders', N'U') IS NOT NULL
DROP TABLE dbo.Orders;

-- create table
CREATE TABLE dbo.Orders
(
OrderId INT IDENTITY(1, 1)
CONSTRAINT PK_Orders_OrderID PRIMARY KEY (OrderId),
CustomerId INT,
Product VARCHAR(50),
Amount DECIMAL(6, 2)
);

-- set variables
DECLARE
@Customers INT = 1000,
@Products INT = 8;

WITH
L0 AS (SELECT 1 AS N UNION ALL SELECT 1),
L1 AS (SELECT 1 AS N FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS N FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS N FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS N FROM L3 AS A CROSS JOIN L3 AS B)

INSERT INTO dbo.Orders (CustomerId, Product, Amount)
SELECT
CustomerId,
CASE ProductID
WHEN 1 THEN 'Monitor'
WHEN 2 THEN 'Laptop'
WHEN 3 THEN 'PC'
WHEN 4 THEN 'Tablet'
WHEN 5 THEN 'Mobile Phone'
WHEN 6 THEN 'Printer'
WHEN 7 THEN 'Scanner'
WHEN 8 THEN 'Headset'
END AS Product,
Amount
FROM
(
SELECT
CustomerId,
CEILING(ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY (SELECT NULL)) / 9.00) AS ProductId,
Amount
FROM
(
SELECT
NTILE(@Customers) OVER(ORDER BY (SELECT NULL)) AS CustomerId,
CAST(ABS(CHECKSUM(NEWID())) / 1000000.00 AS DECIMAL(6, 2)) AS Amount
FROM L4
) AS D
) AS D2;

SELECT
*
FROM dbo.Orders;

/* pivot start */
SELECT
CustomerId,
[Monitor],
[Laptop],
[PC],
[Tablet],
[Mobile Phone],
[Printer],
[Scanner],
[Headset]
FROM
(
SELECT
CustomerId,
Product,
Amount
FROM dbo.Orders
) AS D
PIVOT
(
SUM(Amount) FOR [Product] IN ([Monitor], [Laptop], [PC], [Tablet], [Mobile Phone], [Printer], [Scanner], [Headset])
) AS P
ORDER BY CustomerId;

/* final pivot query */
SELECT
CustomerId,
[Monitor],
[Laptop],
[PC],
[Tablet],
[Mobile Phone],
[Printer],
[Scanner],
[Headset],
[Customer Total]
FROM
(
SELECT
CAST(CustomerId AS VARCHAR(20)) AS CustomerId,
Product,
Amount
FROM dbo.Orders
UNION ALL
SELECT
CAST(CustomerId AS VARCHAR(20)) AS CustomerId,
'Customer Total' AS Product,
SUM(Amount) AS Amount
FROM dbo.Orders
GROUP BY
CustomerId
UNION ALL
SELECT
'Product Total' AS CustomerId,
Product,
SUM(Amount)
FROM dbo.Orders
GROUP BY
Product
) AS D
PIVOT
(
SUM(Amount) FOR [Product] IN ([Monitor], [Laptop], [PC], [Tablet], [Mobile Phone], [Printer], [Scanner], [Headset], [Customer Total])
) AS P
ORDER BY LEN(CustomerId), CustomerId;