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

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

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.``````

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

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``````

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?

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``````

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