Skip to main content
Solved

How to use both COUNT & SUM aggregate functions on 1 line in GI


chris49
Varsity II
Forum|alt.badge.img

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 😊

Best answer by lauraj46

Hi @chris49 ,

You should be able to use a formula similar to this one:

SUM(iif([INPIDetail.VarQty] = 0, 0.0, 1.0)/count([INPIDetail.PIID])

You need the decimal on the iif, otherwise the result of the division will be an integer instead of a decimal.

When you use the aggregate function in the formula, you can leave the aggregate function column blank.  

Hope this helps!

Laura

 

View original
Did this topic help you find an answer to your question?

2 replies

lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 511 replies
  • Answer
  • March 14, 2023

Hi @chris49 ,

You should be able to use a formula similar to this one:

SUM(iif([INPIDetail.VarQty] = 0, 0.0, 1.0)/count([INPIDetail.PIID])

You need the decimal on the iif, otherwise the result of the division will be an integer instead of a decimal.

When you use the aggregate function in the formula, you can leave the aggregate function column blank.  

Hope this helps!

Laura

 


chris49
Varsity II
Forum|alt.badge.img
  • Author
  • Varsity II
  • 54 replies
  • March 14, 2023

Awesome! Thanks @lauraj46 . I ended up doing the view, but will bookmark this for the next time 👌🏻


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings