使用从一列中的数据计算的两列进行查询

I just started checking SQL server in my work, and I just can´t manage to solve this very easy thing.

我刚开始在我的工作中检查SQL服务器，而我却无法解决这个问题。

Lets say I have this Table:

可以说我有这张表：

Model   Serial Pass   Failure  
Y72742  LURC1   0     Ref 5
Y72742  LURC2   0     Ref 10
Y72596  JDID1   1     EMT
Y72596  JDID2   1     EMT
Y72742  LURC3   0     Ref 10
Y72596  JDID3   0     Ref 5
Y72596  JDID4   0     Ref 18
Y72596  JDID5   1     EMT
Y72596  JDID6   0     Ref 18
Y72596  JDID7   0     Ref 5
Y72596  LURC4   1     UMT
Y72596  LURC5   1     UMT

So in the PASS column, the 1 means the unit passed, the 0 means it failed. The Failure Column indicates what was the failure.

所以在PASS列中，1表示单位通过，0表示失败。故障列表示故障原因。

I'm trying to accomplish something like this:

我正在努力完成这样的事情：

Model   Pass   Failed   Percentage
Y72742   2       3         40%
Y72596   1       4         20%

But since is the same column I just can't get it done.

但由于是同一列，我无法完成它。

I tried with a subquery with the "AND" operator, but I got errors. if anyone could help me I would be very grateful.

我尝试使用带有“AND”运算符的子查询，但是我遇到了错误。如果有人能帮助我，我将非常感激。

Regards.

问候。

2 个解决方案

#1

You can construct your query using SUM and GROUP BY:

您可以使用SUM和GROUP BY构造查询：

SELECT
    Model
,   SUM(case when Pass=1 then 1.0 else 0.0 end) AS Pass
,   SUM(case when Pass=1 then 0.0 else 1.0 end) AS Failed
,   100*SUM(case when Pass=1 then 1.0 else 0.0 end)/COUNT(*) AS Percentage
FROM MyTable
GROUP BY Model

#2

MySQL implementation:

MySQL实现：

SELECT 
t.Model, 
SUM(IF(t.Pass=1,1,0)) as Passed, 
SUM(IF(t.Pass=0,1,0)) as Failed,
SUM(IF(t.Pass=1,1,0))/COUNT(t.Pass) as Percentage
FROM MyTable t 
GROUP BY t.Model

#1