Creating a 'total' row and calculating some percentages in SQL pivot table

Other SQL Server Topics
1

I'd really appreciate some help please. I don't know if this is possible but I would like to add some 'Total' row and columns and then calculate percentages of how often a certain piece of equipment was used to my pivot table below.

SELECT * FROM

(
SELECT
I.Incident,
ETT.Description,
SM.StatID


FROM INCIDENT as I
JOIN  EQUIPMENT_USAGE as EU on I.Incident = EU.Incident
JOIN _EquipmentTypeType as ETT on EU.EquipmentType = ETT.code
JOIN _StatMapping as SM on I.ResponsiblePartyStatId = SM.RCCStatD


) As Table1

PIVOT (
      COUNT(incident) 
	  FOR Description
	  IN ([Hosereel],[High expansion],[Other]
 )  As pivottable

Current Output

   STATID        Hosereel High Expansion   Other
    ABC	             2	          6	   5
    DEF	             4	          3	   4         
    GHI	             5            4	   3

What I would like....the percentage coming from the row 'Total' so...2/13*100 = 15%

 statID      Hosereel  |  %  | High Expansion|  %  |  Other    |  %  |   Total
    ABC         2      | 15% |    6          | 46% |     5     | 38% |      13
    DEF         4      | 36% |    3          | 27% |     4     | 36% |      11
    GHI         5      | 41% |    4          | 33% |     3     | 25% |      12
    Total       11                13                     12                 34

Many thanks

2

This is one way of getting the result you are looking for. There may be other more compact ways, but this should work.

;WITH cte AS
(
            --- YOUR ENTIRE ORIGINAL QUERY HERE
)
SELECT 
	Hosereel,
	1.0E * HoseReel/SUM(HoseReel) OVER() AS [Percent],
	[High expansion],
	1.0E * [High expansion]/SUM([High expansion]) OVER() AS [Percent],
	Other,
	1.0E * Other/SUM(Other) OVER() AS [Percent],
	HoseReel+[High expansion]+Other,
	1.0E * (HoseReel+[High expansion]+Other)/
		SUM(HoseReel+[High expansion]+Other) OVER() AS [Percent]
FROM cte
UNION ALL
SELECT 'Total' AS StatId, 
	SUM(Hosereel), 
	NULL, 
	SUM([High Expansion]), 
	NULL, 
	SUM(Other),
	NULL,
	SUM(Hosereel+[High Expansion]+Other)
FROM cte 
ORDER BY 
	CASE WHEN StatId = 'Total' THEN 1 ELSE 0 END,
	Statid -- or other order by conditions here.
3

Hi James, thanks for the reply. Unfortuantely it doesn't work, it's underlining in red all the equipment eg hosereel, high expansion etc

4

Try with the following example, where I have replaced your query with hard-coded data. If this works for you, then replace the contents of cte with your query.

;WITH cte AS
(
            SELECT 'ABC' AS StatId, 2 AS Hosereel,6 AS [High Expansion], 5 AS Other
            UNION ALL
            SELECT 'DEF', 4,3,4
            UNION ALL 
            SELECT 'GHI',5,4,3
),
cte2 AS
(
	SELECT
		StatId, 
		Hosereel,
		1.0E * HoseReel/SUM(HoseReel) OVER() AS [Percent1],
		[High expansion],
		1.0E * [High expansion]/SUM([High expansion]) OVER() AS [Percent2],
		Other,
		1.0E * Other/SUM(Other) OVER() AS [Percent3],
		HoseReel+[High expansion]+Other AS [Tota],
		1.0E * (HoseReel+[High expansion]+Other)/
			SUM(HoseReel+[High expansion]+Other) OVER() AS [Percent4]
	FROM cte
	UNION ALL
	SELECT 'Total' AS StatId, 
		SUM(Hosereel), 
		NULL, 
		SUM([High Expansion]), 
		NULL, 
		SUM(Other),
		NULL,
		SUM(Hosereel+[High Expansion]+Other),
		NULL
	FROM cte 
)
SELECT * FROM cte2
ORDER BY CASE WHEN StatId = 'Total' THEN 1 ELSE 0 END,
StatId
5

that's great thank you. I'm still very much a novice when its comes to SQl so could you please just explain where I incorporate my query into this?

6

Incorporating your original query:

;WITH cte AS
(
-- BEGIN YOUR ORIGINAL QUERY
	SELECT * FROM

	(
	SELECT
		I.Incident,
		ETT.Description,
		SM.StatID
	FROM INCIDENT as I
	JOIN  EQUIPMENT_USAGE as EU on I.Incident = EU.Incident
	JOIN _EquipmentTypeType as ETT on EU.EquipmentType = ETT.code
	JOIN _StatMapping as SM on I.ResponsiblePartyStatId = SM.RCCStatD
	) As Table1

	PIVOT (
		  COUNT(incident) 
		  FOR Description
		  IN ([Hosereel],[High expansion],[Other])
 )  As pivottable 
 
 -- END YOUR ORIGINAL QUERY
),

cte2 AS
(
	SELECT
		StatId, 
		Hosereel,
		1.0E * HoseReel/SUM(HoseReel) OVER() AS [Percent1],
		[High expansion],
		1.0E * [High expansion]/SUM([High expansion]) OVER() AS [Percent2],
		Other,
		1.0E * Other/SUM(Other) OVER() AS [Percent3],
		HoseReel+[High expansion]+Other AS [Tota],
		1.0E * (HoseReel+[High expansion]+Other)/
			SUM(HoseReel+[High expansion]+Other) OVER() AS [Percent4]
	FROM cte
	UNION ALL
	SELECT 'Total' AS StatId, 
		SUM(Hosereel), 
		NULL, 
		SUM([High Expansion]), 
		NULL, 
		SUM(Other),
		NULL,
		SUM(Hosereel+[High Expansion]+Other),
		NULL
	FROM cte 
)
SELECT * FROM cte2
ORDER BY CASE WHEN StatId = 'Total' THEN 1 ELSE 0 END,
StatId
7

@whitz,

You currently have 2 fixed descriptions and a catch all description. Will that ever change?