添加百分比，以多计数在一个SQL SELECT查询(Adding percentages to mu

我有类似下面这一个查询返回的几大罪状之一的SELECT语句。

SELECT  invalidCount = (SELECT COUNT(*) FROM <...a...> WHERE <...b...>),
        unknownCount = (SELECT COUNT(*) FROM <...c...> WHERE <...d...>),
        totalCount   = (SELECT COUNT(*) FROM <...e...> WHERE <...f...>)

这工作得很好，但我想两个百分点列添加到SELECT：

invalidCount * 100 / totalCount AS PercentageInvalid, 
unknownCount * 100 / totalCount AS UnknownPercentage

如何修改我的SELECT语句来处理呢？

Answer 1:

您可以在使用子查询from条款：

select
    s.invalidCount,
    s.unknownCount,
    s.totalCount,
    invalidCount * 100 / s.totalCount as PercentageInvalid,
    unknownCount * 100 / s.totalCount as PercentageUnknown
from
    (select  invalidCount = (SELECT COUNT(*) FROM <...a...> WHERE <...b...>),
        unknownCount = (SELECT COUNT(*) FROM <...c...> WHERE <...d...>),
        totalCount       = (SELECT COUNT(*) FROM <...e...> WHERE <...f...>)) s

Answer 2:

SELECT invalidCount, 
  unknownCount, 
  totalCount, 
  invalidCount * 100 / totalCount AS PercentageInvalid, 
  unknownCount * 100 / totalCount AS UnknownPercentage 
FROM 
    (
      SELECT  invalidCount = (SELECT COUNT(*) FROM <...a...> WHERE <...b...>),  
      unknownCount = (SELECT COUNT(*) FROM <...c...> WHERE <...d...>),     
      totalCount       = (SELECT COUNT(*) FROM <...e...> WHERE <...f...>)
    )

Answer 3:

下面是使用OVER子句是非常酷的一种不同的方法 - 非常有效的。 看看对AdventureWorks的这个例子：

SELECT DISTINCT CardType 
        ,COUNT(*) OVER (PARTITION BY CardType) AS TypeCount
        ,COUNT(*) OVER (PARTITION BY 1) AS TotalCount
        ,COUNT(*) OVER (PARTITION BY CardType) / CAST(COUNT(*) OVER (PARTITION BY 1) AS float) AS TypePercent
FROM Sales.CreditCard

Answer 4:

我肯定会跟Mike的解决方案吧！ 更优雅，更高效。

但我看不出有任何理由不使用GROUP BY这里。 这将使其更加优雅，更高效。 我建议摆脱多余的“COUNT（*）OVER（PARTITION BY 1）”，并总结了数代替。

SELECT CardType 
       , COUNT(*) AS TypeCount
       , SUM(COUNT(*)) OVER () AS TotalCount
       , COUNT(*) * 1.0 / SUM(COUNT(*)) OVER () AS TypePercent
FROM Sales.CreditCard
GROUP BY CardType

笔记：

• “PARTITION BY 1” 是多余的，所以省略。
• 以CardType-计数SUM从没有分区再次计数等全表拯救我们。
• * 1.0是用来代替铸造。
• 查询没有测试，但它应该工作..

Answer 5:

SELECT  invalidCount = (SELECT COUNT(*) FROM <...a...> WHERE <...b...>),
        unknownCount = (SELECT COUNT(*) FROM <...c...> WHERE <...d...>),
        totalCount       = (SELECT COUNT(*) FROM <...e...> WHERE <...f...>)
INTO #tmp

SELECT invalidCount,
       unknownCount,
       totalCount,
       invalidCount * 100 / totalCount AS PercentageInvalid, 
       unknownCount * 100 / totalCount AS UnknownPercentage
FROM #tmp

DROP TABLE #tmp

文章来源: Adding percentages to multiple counts in one SQL SELECT Query