I’m working on a report to calculate the physical inventory count accuracy.

I’m able to calculate the total number of lines counted, and how many of those lines had variances, but I would like to display the accuracy as a % value. This requires me to do some calculations on a single line, and I can’t quite figure out how to achieve that.

I have the following so far:

The corresponding SQL for the required outcome is:

`SELECT`

`INPIDetail`.`PIID`,

`INPIHeader`.`CountDate`,

COUNT(*) AS `TotalLines`,

SUM(CASE WHEN `INPIDetail`.`VarQty` = 0 THEN 0 ELSE 1 END) AS `Variances`,

ROUND(((COUNT(*) - SUM(CASE WHEN `INPIDetail`.`VarQty` = 0 THEN 0 ELSE 1 END)) / COUNT(*)) * 100, 2) AS `Accuracy`

FROM `INPIDetail` `INPIDetail`

INNER JOIN `INPIHeader` `INPIHeader`

ON `INPIHeader`.`PIID` = `INPIDetail`.`PIID`

AND `INPIHeader`.`CompanyID` = `INPIDetail`.`CompanyID`

WHERE `INPIDetail`.`CompanyID` = X

GROUP BY `INPIDetail`.`CompanyID`, `INPIDetail`.`PIID`, `INPIHeader`.`CountDate`

;

As seen above, I have the `SUM(CASE WHEN `INPIDetail`.`VarQty` = 0 THEN 0 ELSE 1 END)`

part of the query figured out, but the part that I’m struggling with is the ability to subtract this amount from the count of all records.

Any ideas on how I can achieve the desired outcome?

PS. I know I can very easily do this with a SQL view, and then create my own DAC and then simply just build a GI off of that DAC, but I’m wondering if there’s a way to achieve this without doing all of that.

Thanks in advance