perform math on multiple SELECT results in SQL

Question

Similar to this question How does one perform math on multiple SELECT results?

Cant make mine work.

I want to determine the % of Shipments that are were sent on time.

To do so, I know I have to:

1) Select the total shipments sent.

Select COUNT(*) AS TotalShipments From tbl_LISC

2) Select the total shipments sent OnTime

Select COUNT(*) AS TotalOnTime From tbl_LISC WHERE tbl_LISC.LISC = 100

3) Divide the 2 results and multiply by 100 ( (TotalOnTime/TotalShipments)*100 )

Following the answer of the thread as an example I tried

SELECT TotalShipped, TotalOnTime, TotalShipped/TotalOnTime as LISC
FROM (SELECT COUNT(Select *From tbl_LISC) AS TotalShipped,
             COUNT(*) as TotalOnTime
          FROM tbl_LISC
          WHERE LISC = 0) 

Answer 1

Your real problem is integer versus floating point math. When both numbers of the expression are integers, the result will also be an integer:

select 2/3 -- 0

So, in order to preserve decimal precision, simply make one of the numbers in the expression a decimal/float value:

select 2.0/3 -- 0.666666

So, try this on for size:

select
 sum(iif(tbl_LISC.LISC = 100, 1, 0)) as OnTime
,count(*) as Total
,sum(iif(tbl_LISC.LISC = 100, 1.0, 0.0)) / nullif(count(*), 0) as Percentage
from tbl_LISC as l