两个选择计数查询，然后计算百分比 [英] Two select count queries and then calculate percentage

问题描述

我想结合两个查询

SELECT COUNT(*) FROM abc;
SELECT COUNT(Status) FROM ABC WHERE Status='Active';

然后计算百分比(将第二个查询除以第一个查询).我想在一个查询中实现这一目标.到目前为止，我已经尝试过:

And then calculate the percentage (by taking the 2nd query divided by first query). I'd like to achieve this in one single query. What i've attempted so far:

SELECT COUNT(*) AS A FROM abc
UNION
SELECT COUNT(Status) AS B FROM ABC WHERE Status='Active';
UNION 
SELECT(COUNT(Status)*100/SELECT COUNT(*) FROM abc)) AS %ofAB FROM abc WHERE Status='Active'

我得到的是

A
--
31
36
86,11111111

我想要什么:

A  | B  | %ofAB
---------------------
36 | 31 | 86,1111111%    

推荐答案

这应该给您您想要的东西:

This should give you what you want:

SELECT
    COUNT(*) AS TotalCount,
    SUM(IIF(Status = 'Active', 1, 0)) AS ActiveCount,
    ROUND((SUM(IIF(Status = 'Active', 1, 0)) * 100/ COUNT(*)),2) AS PctActive
FROM
    Abc

没注意到这是用于Access的.我不知道CAST是否在Access中可用，因此您可能需要使用等效函数来确保整数不只是产生1或0.Access可能会自动将除法转换为十进制，但在SQL Server中却没有.

Didn't notice that this was for Access. I don't know if CAST is available in Access, so you may need to use an equivalent function to make sure that the integers don't simply yield 1 or 0. It's possible that Access will convert a division into a decimal automatically, but in SQL Server it does not.

这篇关于两个选择计数查询，然后计算百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！