需要计算 SQL Query 中总计数的百分比 [英] Need to calculate percentage of total count in SQL Query
问题描述
我有一个表格,其中有两列,一个是日期时间列 (Test_Complete),另一个是字母数字记录 ID 列 (RecordID).
I have a a table where I have two columns, one is a Date Time column (Test_Complete) and another is alphanumeric Record ID column (RecordID).
我需要准备每月处理的记录 ID 计数.我已经为此创建了一个查询.
I need to prepare a count of recordIDs which were processed on a monthly basis. I have already created a query for that.
SELECT (Format([Test_Complete],"mmm"" '""yy")) AS Evaluation_Month,
Count(tbl_TestStatus.Record_ID) AS CountOfRecord_ID
FROM tbl_TestStatus
WHERE (((tbl_TestStatus.[Test_Complete]) Is Not Null))
GROUP BY (Format([Test_Complete],"mmm"" '""yy")),
(Year([Test_Complete])*12+Month([Test_Complete])-1);
此查询运行良好,并为我提供如下输出:
This query works well and gives me output like this:
Evaluation_Month CountOfRecord_ID
------------------ -----------------
Jan'12 20
Feb'12 90
Mar'12 40
Apr'12 50
现在我需要的是计算 CountOfRecord_ID 值相对于每个 Evaluation_Month 的百分比,并将该百分比附加到 Evaluation_Month 数据中的值.
Now what I need is to calculate the percentage of CountOfRecord_ID value against each Evaluation_Month and append the percentage with the value in Evaluation_Month data.
在上面的结果集中,所有 CountOfRecord_ID 的总和为 200.因此需要将 200 视为 100% 来计算百分比,这样我的结果看起来像这样:
In the above resultset, the sum of all the CountOfRecord_ID is 200. so the percentage needs to be calculated considering 200 as 100%, such that my result looks like this:
Evaluation_Month CountOfRecord_ID
------------------ -----------------
Jan'12 (10%) 20
Feb'12 (45%) 90
Mar'12 (20%) 40
Apr'12 (25%) 50
如何修改我的 SQL 查询以实现此目的?
How can I modify my SQL query to achieve this?
推荐答案
您只需要在您的 select
语句中添加一个子查询字段"和记录总数.像这样:
You only need to add a "subquery field" in your select
statement with the total count of records. Something like this:
SELECT
(Format([Test_Complete],"mmm"" '""yy")) AS Evaluation_Month,
Count(tbl_TestStatus.Record_ID) AS CountOfRecord_ID,
Count(tbl_TestStatus.Record_ID) / (select count(tbl_testStatus.recordId
from tbl_testStatus
where tbl_testStatus.test_complete is not null) as percent
FROM
tbl_TestStatus
WHERE
(((tbl_TestStatus.[Test_Complete]) Is Not Null))
GROUP BY
(Format([Test_Complete],"mmm"" '""yy")),
(Year([Test_Complete])*12+Month([Test_Complete])-1);
这篇关于需要计算 SQL Query 中总计数的百分比的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!