计算查询中的百分比-Access SQL [英] Calculate Percentages In Query - Access SQL

问题描述

我正在尝试量化一些东西.这是一个示例(当然是简化的):

I'm trying to quantify some things. Here's a sample (simplified of course):

tblParent: { Number, Name }
tblChild: { Number, Name, ParentNumber, Criterion }

我想计算具有相同 ParentNumber 的Children的总数(使用Group By和Count(1)很容易)，但是问题是将该数字与具有相同 ParentNumber 的子代，具有 Criterion ="Something" .

I'd like to count the total number of Children with the same ParentNumber (easy using a Group By and a Count(1) ), but the problem is comparing that number with the number of children with the same ParentNumber who have Criterion = "Something".

这是我到目前为止所拥有的:

Here's what I have so far:

SELECT "Criterion = 1" AS CritDesc,
COUNT(1) AS Total,
ParentNumber AS Parent,
( COUNT(1) / (SELECT Total FROM [TotalCountingQuery]) ) AS Percentage
FROM tblChild
WHERE Criterion = 1
GROUP BY ParentNumber;

[TotalCountingQuery] 只需对每个ParentNumber的所有子代进行计数，然后将总数放入 Total

[TotalCountingQuery] simply counts all Children with each ParentNumber and puts that total into Total

SELECT COUNT(1) AS Total FROM tblChild GROUP BY ParentNumber;

因此，我尝试使用TOTAL计数并找出有多少(按百分比计)具有Criterion = 1.但是，由于它使用的是子查询，因此该子查询不能返回多个Total.我需要它返回每个ParentNumber的总数，以用于每个具有该ParentNumber的子代的百分比计算中.

So I'm trying to use that TOTAL count and figure out how many (as a percentage) have Criterion=1. But since it is using a subquery, the subquery cannot return more than one Total. I need it to return the total for each ParentNumber, to be used in the Percentage calc for each Child having that ParentNumber.

最终结果应为:

CritDesc   |    Total    |   Parent    |   Percentage
```````````````````````````````````````````````````````
Criterion=1|      2      |   45011     |      0.333    // Means there should be 6 Children with 45011 as parent
Criterion=1|      4      |   43255     |       0.9
Criterion=1|      1      |   59056     |      0.44

我有几个这样的查询，将它们合并到报表中以生成完整的报表，并按父级分组，显示所有父级的所有"Criterion = X".那行得通，我只需要以上工作即可.

I have several of these queries which I union in a report to produce a full report, grouped by Parent, showing all "Criterion=X" for all Parents. That's working, I just need the above working.

想法?

为此我尝试加入:

SELECT "Criterion=1" AS CritDesc,
COUNT(c.Number) AS Total,
ParentNumber AS Parent,
COUNT(c.Number)/q.Total AS Percentage
FROM tblChild AS c INNER JOIN tblParent AS q ON c.ParentNumber = q.Number
GROUP BY ParentNumber

这引发:您试图执行查询...'Count(1)/q.Total'作为聚合函数的一部分.将其添加到group-by语句将引发在GROUP BY中不能具有聚合功能 ...

This throws: You tried to execute a query ... 'Count(1)/q.Total' as part of an aggregate function. Adding it to the group-by statement throws Cannot have aggregate function in GROUP BY...

推荐答案

您的子查询没有where子句，因此可以计算所有记录，但是您可以在没有子查询的情况下进行操作

Your subquery has no where clause and thus counts all records, but you can do it without subquery

SELECT
    "Criterion = 1" AS CritDesc,
    SUM(IIf(Criterion = 1, 1, 0)) AS NumCrit,
    COUNT(*) AS TotalNum,
    SUM(IIf(Criterion = 1, 1, 0)) / COUNT(*) AS Percentage,
    ParentNumber AS Parent
FROM
    tblChild
GROUP BY
    ParentNumber;

注意:我放弃了WHERE条款.相反，我通过合计 Criterion = 1 和 0 的 1 来计数满足条件的记录.这使我可以同时使用 Count(*)获得每个 ParentNumber 的总数.

Note: I dropped the WHERE-clause. Instead I am counting the records fulfilling the criterion by summing up 1 for Criterion = 1 and 0 otherwise. This allows me to get the total number per ParentNumber at the same time with Count(*).

更新

您可能还希望为没有孩子的父母取得成果.在这种情况下，您可以使用外部联接

You might want to get results for parents having no children as well. In that case you can use an outer join

SELECT
    "Criterion = 1" AS CritDesc,
    SUM(IIf(C.Criterion = 1, 1, 0)) AS NumCrit,
    COUNT(C.Number) AS TotalNumOfChildren,
    SUM(IIf(C.Criterion = 1, 1, 0)) / COUNT(*) AS Percentage,
    P.Number AS Parent
FROM
    tblChild AS C
    LEFT JOIN tblParent AS P
        ON C.ParentNumber = P.Number  
GROUP BY
    P.Number;

请注意，我得到具有 Count(C.Number)的子代总数，因为 Count(*)也会对没有子代的记录进行计数并产生 1 .但是，在百分比计算中，为了避免被零除，我将除以 Count(*).在这种情况下，结果仍然是正确的，因为 Criterion = 1 的记录总数将为零.

Note that I get the total number of children with Count(C.Number) as Count(*) would count records with no children as well and yield 1 in that case. In the percentage calculation, however, I divide by Count(*) in order to avoid a division by zero. The result will still be correct in that case, since the sum of records with Criterion = 1 will be zero.

这篇关于计算查询中的百分比-Access SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！