query or CASE help 1

[LloydDobler]

Looking for some help on this query. I need to count a column (parts) where the stock on hand (SOH) is => the safety stock (SAFETY), then divide that into a count of total parts. Here's table structure

PLANT PART SOH SAFETY
1 abc 50 50
1 xyz 40 50
1 xxxx 20 0
1 zzzz 0 0

Based on this, I should get %75 as a 'Percent of Stock at Safety Level'

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[rac2]

SELECT SUM(
           CASE
             WHEN soh => safety THEN 1
             ELSE 0
           END
          ) AS "SafetyLevel",
       COUNT(*) AS "StockItems",
       ( SUM(
           CASE
             WHEN soh => safety THEN 1.0
             ELSE 0.0
           END
          ) / COUNT(*) ) * 100 AS "Percent of Stock at Safety Level"

FROM Inventory

will give you the number. You may wish to CAST it to DECIMAL for the desired number of decimal places.

SELECT SUM(
           CASE
             WHEN soh => safety THEN 1
             ELSE 0
           END
          ) AS "SafetyLevel",
       COUNT(*) AS "StockItems",
       CAST(   ( ( SUM(
           CASE
             WHEN soh => safety THEN 1.0
             ELSE 0.0
           END
          ) / COUNT(*) ) * 100 ) AS DECIMAL(10,1)  ) AS "Percent of Stock at Safety Level"

FROM Inventory

 

[LloydDobler]

That worked great. Thanks rac2.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."