# 需要在SQL查询中计算出总计数的百分比[英] Need to calculate percentage of total count in SQL Query

### 问题描述

```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
```

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

## 推荐答案

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

### 问题描述

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