Adding percentages to multiple counts in one SQL S

问题:

I have a SELECT statement similar to the one below which returns several counts in one query.

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

This works fine but I wanted to add two percentage columns to the SELECT:

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

How do I modify my SELECT statement to handle this?

回答1:

You can use a subquery in the from clause:

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

回答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...>)
    )

回答3:

Here is a different approach using the OVER clause that is very cool - very efficient. Check out this example against 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

回答4:

I would definitely go with Mike's solution! Much more elegant and more efficient.

But I don't see any reason not to use GROUP BY here. That would make it even more elegant and more efficient. I would suggest getting rid of a redundant "COUNT(*) OVER (PARTITION BY 1)", and sum the counts instead.

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

Notes:

• "PARTITION BY 1" was redundant, so omitted.
• Taking SUM of CardType-Counts saves us from counting the whole table once again without partitioning.
• *1.0 is used instead of casting.
• Query not tested, but it should work..

回答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