在一个 SQL SELECT 查询中为多个计数添加百分比 [英] Adding percentages to multiple counts in one SQL SELECT Query

问题描述

我有一个类似于下面的 SELECT 语句，它在一个查询中返回多个计数.

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

这很好用，但我想在 SELECT 中添加两个百分比列:

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

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

如何修改我的 SELECT 语句来处理这个问题?

How do I modify my SELECT statement to handle this?

推荐答案

您可以在 from 子句中使用子查询:

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

这篇关于在一个 SQL SELECT 查询中为多个计数添加百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！