需要在SQL查询中计算出总计数的百分比

本文是小编为大家收集整理的关于需要在SQL查询中计算出总计数的百分比的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

我有一个表格，其中有两个列，一个是日期时间列(test_complete)，另一个是字母数字记录ID列(recordID).

我需要准备每月处理的记录ID数.我已经为此创建了一个查询.

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

此查询效果很好，并给了我这样的输出:

Evaluation_Month     CountOfRecord_ID
------------------   -----------------
 Jan'12                   20
 Feb'12                   90
 Mar'12                   40
 Apr'12                   50

现在，我需要的是计算每个essionuation_month的countofrecord_id值的百分比，并以estaruation_month数据中的值附加百分比.

在上面的结果集中，所有CountofRecord_ID的总和为200.因此，考虑到200为100％，需要计算百分比，以使我的结果看起来像这样:

Evaluation_Month     CountOfRecord_ID
------------------   -----------------
 Jan'12 (10%)                20
 Feb'12 (45%)                90
 Mar'12 (20%)                40
 Apr'12 (25%)                50

如何修改我的SQL查询以实现此目标?

推荐答案

您只需要在select语句中添加一个"子查询字段"，并具有记录总数.这样的东西:

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

本文地址：https://itbaoku.cn/post/2581553.html

百度翻译此文有道翻译此文

问题描述

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

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

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.

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

How can I modify my SQL query to achieve this?

推荐答案

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

查看全文

上一篇：MS Access：为什么当文本框被禁用时，标签格式会改变？

下一篇：访问子表格不能刷新显示的记录