将百分比列添加到MS Access查询 [英] Adding a percent column to MS Access Query

问题描述

我正在尝试添加一列来计算MS Access查询中不同产品的百分比.基本上，这是我要访问的查询的结构:

I'm trying to add a column which calculates percentages of different products in MS Access Query. Basically, this is the structure of the query that I'm trying to reach:

用于查找我使用的百分比的公式是:([[一个产品的Q总数]/[所有产品的总数]] * 100，但是当我尝试使用表达式生成器时(因为我的SQL技能很基本)， MS Access进行计算..

The formula for finding the percent I use is: ([Total Q of a Product]/[Totals of all Products])*100, but when I try to use the expression builder (since my SQL skills are basic) in MS Access to calculate it..

= [CountOfProcuts]/Sum([CountOfProducts])

..我收到一条错误消息在GROUP BY子句中不能具有聚合函数.(并且表达式在此处)".我还用两个查询尝试了该选项:一个查询仅计算总计，另一个查询使用第一个查询计算百分比，但是结果是相同的.

..I receive an error message "Cannot have aggregate function in GROUP BY clause.. (and the expression goes here)". I also tried the option with two queries: one that calculates only the totals and another that use the first one to calculate the percentages, but the result was the same.

如果有人可以帮助我，我将不胜感激.

I'll be grateful if someone can help me with this.

推荐答案

通过此查询，您可以获取所需输出中除最后一行以外的所有内容.

You can get all but the last row of your desired output with this query.

SELECT
    y.Product,
    y.Total,
    Format((y.Total/sub.SumOfTotal),'#.##%') AS Percentage
FROM
    YourTable AS y,
    (
        SELECT Sum(Total) AS SumOfTotal
        FROM YourTable
    ) AS sub;

由于该查询不包含JOIN或WHERE条件，因此它返回表和子查询的单行之间进行交叉联接.

Since that query does not include a JOIN or WHERE condition, it returns a cross join between the table and the single row of the subquery.

如果您需要问题示例的最后一行，则可以UNION与另一个查询一起执行，该查询将返回所需的伪造行.在此示例中，我使用了自定义对偶表，该表被设计为始终仅包含一行.但是您可以替换另一个返回单行的表或查询.

If you need the last row from your question example, you can UNION the query with another which returns the fabricated row you want. In this example, I used a custom Dual table which is designed to always contain one and only one row. But you could substitute another table or query which returns a single row.

SELECT
    y.Product,
    y.Total,
    Format((y.Total/sub.SumOfTotal),'#.##%') AS Percentage
FROM
    YourTable AS y,
    (
        SELECT Sum(Total) AS SumOfTotal
        FROM YourTable
    ) AS sub
UNION ALL
SELECT
    'Product',
    DSum('Total', 'YourTable'),
    '100%'
FROM Dual;

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