SQL Tutorial - How to add total rows and columns to a Pivot Query
Вставка
- Опубліковано 25 лип 2022
- 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; - Наука та технологія
Very well explained ❤❤
Super, especially the union all to add the rows to the bottom of the original query, definitely gonna steal that 👍
Thanks Brian, much appreciated as always.
Thanks helped a lot
Glad I could help.
Great thanks for this video!! This is the simplest and clearest explaination of pivot with total. It help me solve my problem.
Excellent 👍
Thanks bearded dev for this video it helped me to resolve mine issue. 👍
Excellent 👍
Thank You. Just saved my day !!! ;-)
Fantastic.
Hey there, Kudos to your great efforts in this channel.
I am a new sql learner with simple enquiry.
Could i install on my pc only sql server management studio without an sql server?
The reason that our company has a dedicated server machine, so would i need only to install
the sql studio to access the server?
Thanks for taking care of my enquiry.
Hi Hasan, yes, you can just install SQL Server Management Studio and that will allow you to connect to databases.
This was EXTREMELY helpful. Mine was a little different and I am not getting a grand, grand total. The box intersecting the total columns is blank. Do you know of way to populate that?
Hi Lisa, that's not something we can do within the PIVOT itself as it's based on customer, we would have to use a derived table or CTE then perform another aggregation on top of it.
@@BeardedDevData Thank you for getting back to me - I really appreciate it.
No problem, a sample of what I mean would look like this:
WITH CTE
AS
(
)
SELECT
*,
SUM(Total) OVER(ORDER BY (SELECT NULL)) AS GrandTotal
FROM CTE;
The downside is this would be added as a new column
Or you could take a look at the alternative to PIVOT, this will give more control over what you are aggregating: ua-cam.com/video/xPMbgM8Eb2k/v-deo.html
I need some help if in your example instead of product if its date (like monthly date '2012-03-01', '2012-03-02','2012-03-03','2012-03-04', so on.. upto 2012-03-31' ) and instead of customer total column i required monthly day count with respect to day value means if march month having total 31 day in that 25 day having value greater than 0 and remaining 6 day have null or 0 value then monthly day count should be 25
So please help in this case
It would depend on what you want the output to look like, it sounds like you'd need a SUM(CASE ... as a starting point.
If my table contains customer details having birthdate with other information
Then my requirement is that
I need birth date wise data (consider only 1 month in this case) with birthdate count column as Total
Like
Below
Column name :--- '2013-03-01' having value 4 (4 is count of customer w.r.t. birthdate)
'2013-03-02' having value 3
'2013-03-03' having value 5
Remaining column name :-
'2013-03-04' to '2013-03-31' having value 0 or null
Last column name :- birthdate_count=3 having count as per above (because three columns have value greater than 0 and remaining have 0 value)
This sounds more of a simple aggregation, first you'd need to manipulate the birthdate to get a consistent date for each month then group by it with a count.
Hi
@@BeardedDevData
CREATE TABLE yourtable
([role] varchar(17), [familyname] varchar(8), [givenname] varchar(5), [skill] int, [level] varchar(15), [id] int, [date] datetime)
;
INSERT INTO yourtable
([role], [familyname], [givenname], [skill], [level], [id], [date])
VALUES
('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'),
('Vision Superv', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-07 00:00:00'),
('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'),
('Vision Supervor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-01 00:00:00')
;
select date,
(case when date is null then count(distinct date) else count(*) end ) daywisetotal
from yourtable where date between '2013-03-01' and '2013-03-31'
group by cube (date)
having count(*)>0 ;
=> Output (this is the output what I required)
date daywisetotal
----------------------- ------------
2013-03-01 00:00:00.000 1
2013-03-07 00:00:00.000 1
2013-03-27 00:00:00.000 2
NULL 3
Need same output in pivot
Like
Total '2013-03-01' '2013-03-07' '2013-03-27'
Row1 :- 3 1 1 2
But I am getting output in more than 1 row .
Can please help me for getting same output in pivot ?
CREATE TABLE yourtable
([role] varchar(17), [familyname] varchar(8), [givenname] varchar(5), [skill] int, [level] varchar(15), [id] int, [date] datetime)
;
INSERT INTO yourtable
([role], [familyname], [givenname], [skill], [level], [id], [date])
VALUES
('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'),
('Vision Superv', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-07 00:00:00'),
('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'),
('Vision Supervor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-01 00:00:00')
;
select date,
(case when date is null then count(distinct date) else count(*) end ) daywisetotal
from yourtable where date between '2013-03-01' and '2013-03-31'
group by cube (date)
having count(*)>0 ;
=> Output (this is the output what I required)
date daywisetotal
----------------------- ------------
2013-03-01 00:00:00.000 1
2013-03-07 00:00:00.000 1
2013-03-27 00:00:00.000 2
NULL 3
Need same output in pivot
Like
Total '2013-03-01' '2013-03-07' '2013-03-27'
Row1 :- 3 1 1 2
But I am getting output in more than 1 row .
Can please help me for getting same output in pivot ?