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

CodePudding user response：

You could use 'UNION' to join two queries together to give you a total row; it'd be something like:

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

UNION

SELECT "Totals:", 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 "state"."DISPLAYSTATE")d

CodePudding user response：

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 together with "conditional aggregation" 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 D.COMPONENTNAME WITH ROLLUP
ORDER BY
    CASE WHEN GROUPING(product.COMPONENTNAME) = 0 THEN 1 ELSE 2 END,
    product.COMPONENTNAME

Results:

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