对SQL中的多个SELECT结果执行数学运算

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.

1)选择发送的总出货量。

Select COUNT(*) AS TotalShipments From tbl_LISC

2) Select the total shipments sent OnTime

2)选择准时发货的总数量

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

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

3)将2个结果除以100 ((TotalOnTime/ total发货量)*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)

1 个解决方案

#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

#1