从同一表中的两个不同where语句中使用组by创建计算列

Question

我试图创建一个sql结果，它显示客户备份作业的成功率(%)。

我想以某种方式将以下查询结合起来以实现这一点：

select [colCustomerID],count(*) as Totalbackups FROM [BackupStat].[dbo].[tblJobSummary] group by [colCustomerID]

select [colCustomerID],count(*) as Failedbackups FROM [BackupStat].[dbo].[tblJobSummary] where [colStatus] !='Success' and [colStatus] !='Warning' group by [colCustomerID]

第一个查询的结果如下所示：

colCustomerID   Totalbackups
EXK-1670001 315
INK-2325001 8
INK-995001  5
INK-2326001 10
INK-1729001 7
INK-237001  2
GBG-3795001 4
INK-3422001 4
INK-3768001 138
INK-3780001 10

Answer 1

您可以创建两个子查询，然后执行计算。您可能希望更改十进制转换中的精度和比例尺。

编辑:添加了Totalbackups列。

select 
    tb.colCustomerID,
    tb.Totalbackups,
    convert(decimal(5,2), fb.Failedbackups)/convert(decimal(5,2), tb.Totalbackups)
from    
(
    select 
        [colCustomerID],
        count(*) as Totalbackups 
    FROM [BackupStat].[dbo].[tblJobSummary] 
    group by [colCustomerID]
) tb
    inner join 
        (
            select 
                [colCustomerID],
                count(*) as Failedbackups 
            FROM [BackupStat].[dbo].[tblJobSummary] 
            where [colStatus] !='Success' 
                and [colStatus] !='Warning' 
            group by [colCustomerID]
        ) fb
            on tb.colCustomerID = fb.colCustomerID

希望这能有所帮助！