Add Total Row in SQL Query (Pivot)

We are using a asset management software and unfortunatly unable to get the proper support in DIY implementation. I was looking to get an asset report (Laptop Count based on model and state).

With the help of google I manage to create a pivot for the required report. Now, I am stuck with the last part of the task. which is to get a total row for asset state e.g. (In - Store, New - In Store) at the bottom.

This is the Query and I am unable to understand how to add the total row.

SELECT 
*, 
ISNULL([In Use], 0.) + ISNULL([Used - In Store], 0.) + ISNULL([In Store], 0.) + ISNULL([New - In Store], 0.) + ISNULL([Damaged], 0.) + ISNULL([Faulty], 0.) AS TOTAL 
FROM 
(
SELECT 
max("product"."COMPONENTNAME") AS "Product", 
max("state"."DISPLAYSTATE") AS "Asset State", 
count("resource"."RESOURCENAME") AS "Asset Count" 
FROM 
"Resources" "resource" 
LEFT JOIN "ComponentDefinition" "product" ON "resource"."COMPONENTID" = "product"."COMPONENTID" 
LEFT JOIN "ResourceState" "state" ON "resource"."RESOURCESTATEID" = "state"."RESOURCESTATEID" 
LEFT JOIN "ResourceOwner" "rOwner" ON "resource"."RESOURCEID" = "rOwner"."RESOURCEID" 
LEFT JOIN "ResourceAssociation" "rToAsset" ON "rOwner"."RESOURCEOWNERID" = "rToAsset"."RESOURCEOWNERID" 
LEFT JOIN "SDUser" "sdUser" ON "rOwner"."USERID" = "sdUser"."USERID" 
LEFT JOIN "AaaUser" "aaaUser" ON "sdUser"."USERID" = "aaaUser"."USER_ID" 
WHERE 
product.COMPONENTNAME LIKE ('Thinkpad%') 
GROUP BY 
"product"."COMPONENTNAME", 
"state"."DISPLAYSTATE"
) d pivot (
sum("Asset Count") for "Asset State" in (
[In Use], [Used - In Store], [In Store], 
[New - In Store], [Damaged], [Faulty]
)
) piv

You can use GROUP BY ... WITH CUBE to automatically generate totals in both dimensions. This will add a totals row and will also eliminate the need to explicitly calculate the totals column.

SELECT piv.*
FROM (
SELECT 
ISNULL(product."COMPONENTNAME", 'Totals:') AS "Product", 
ISNULL(state."DISPLAYSTATE", 'TOTAL') AS "Asset State", 
count(resource."RESOURCENAME") AS "Asset Count" 
FROM 
"Resources" "resource" 
LEFT JOIN "ComponentDefinition" "product" ON "resource"."COMPONENTID" = "product"."COMPONENTID" 
LEFT JOIN "ResourceState" "state" ON "resource"."RESOURCESTATEID" = "state"."RESOURCESTATEID" 
LEFT JOIN "ResourceOwner" "rOwner" ON "resource"."RESOURCEID" = "rOwner"."RESOURCEID" 
LEFT JOIN "ResourceAssociation" "rToAsset" ON "rOwner"."RESOURCEOWNERID" = "rToAsset"."RESOURCEOWNERID" 
LEFT JOIN "SDUser" "sdUser" ON "rOwner"."USERID" = "sdUser"."USERID" 
LEFT JOIN "AaaUser" "aaaUser" ON "sdUser"."USERID" = "aaaUser"."USER_ID" 
WHERE 
product.COMPONENTNAME LIKE ('Thinkpad%') 
GROUP BY 
product."COMPONENTNAME", 
state."DISPLAYSTATE"
WITH CUBE
) d
pivot (
sum("Asset Count") for "Asset State" in (
[In Use], [Used - In Store], [In Store], 
[New - In Store], [Damaged], [Faulty],
[TOTAL])
) piv
ORDER BY
CASE WHEN piv.Product = 'Totals:' THEN 2 ELSE 1 END,
piv.Product

ISNULL() is used to assign labels to the grouped product and status names, which would otherwise be null. [Total] has also been added to the pivot list and removed from the final select list. You may need to edit the select list to add ISNULL() functions if you want to replace null values with zeros. (E.g., ISNULL([In Use], 0) AS [In Use], ...)

Results:

You can also code a similar result using GROUP BY ... WITH ROLLUP for the rows together with "conditional aggregation" to define the columns as a replacement for the pivot.

SELECT
ISNULL(product.COMPONENTNAME, 'Total') AS Product,
COUNT(CASE WHEN state.DISPLAYSTATE = 'In Use' THEN 1 END) AS [In Use],
COUNT(CASE WHEN state.DISPLAYSTATE = 'Used - In Store' THEN 1 END) AS [Used - In Store],
COUNT(CASE WHEN state.DISPLAYSTATE = 'In Store' THEN 1 END) AS [In Store],
COUNT(CASE WHEN state.DISPLAYSTATE = 'New - In Store' THEN 1 END) AS [New - In Store],
COUNT(CASE WHEN state.DISPLAYSTATE = 'Damaged' THEN 1 END) AS [Damaged],
COUNT(CASE WHEN state.DISPLAYSTATE = 'Faulty' THEN 1 END) AS [Faulty],
COUNT(*) AS TOTAL
FROM 
"Resources" "resource" 
LEFT JOIN "ComponentDefinition" "product" ON "resource"."COMPONENTID" = "product"."COMPONENTID" 
LEFT JOIN "ResourceState" "state" ON "resource"."RESOURCESTATEID" = "state"."RESOURCESTATEID" 
LEFT JOIN "ResourceOwner" "rOwner" ON "resource"."RESOURCEID" = "rOwner"."RESOURCEID" 
LEFT JOIN "ResourceAssociation" "rToAsset" ON "rOwner"."RESOURCEOWNERID" = "rToAsset"."RESOURCEOWNERID" 
LEFT JOIN "SDUser" "sdUser" ON "rOwner"."USERID" = "sdUser"."USERID" 
LEFT JOIN "AaaUser" "aaaUser" ON "sdUser"."USERID" = "aaaUser"."USER_ID" 
WHERE 
product.COMPONENTNAME LIKE ('Thinkpad%') 
GROUP BY product.COMPONENTNAME WITH ROLLUP
ORDER BY
CASE WHEN GROUPING(product.COMPONENTNAME) = 0 THEN 1 ELSE 2 END,
product.COMPONENTNAME

"Conditional aggregation" is an informal term for usieng aggregation functions like SUM() or COUNT() together with a CASE expression. If the condition is true, the THEN clause provides the data to be aggregated. When false, the implicit ELSE null value is ignored.

Results:

See this db<>fiddle for examples of both using simplified test data.