两个选择计数查询，然后计算百分比

本文介绍了两个选择计数查询，然后计算百分比的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想结合两个查询

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.