将每行的值除以此列的SUM

I have a query that display items with name and its weight with respect to the entire bucket of items. These buckets are sub-categorized so I want to convert total weight to sub-category weight.

我有一个查询，显示项目的名称和重量相对于整个项目桶。这些桶是分类的，所以我想将总重量转换为子类别重量。

Pre-adjustment, with weight with respect to entire bucket looks as follows.

相对于整个桶的重量的预调整如下。

InvestmentName  Weight
Ac              0.01236194
AG              0.04102656
Ca              0.02483226
DF              0.02951128
Ea              0.003295817
GE              0.005415929

This is the desired result I quickly did in Excel, which I could not achieve with a query right now. The numbers sum to 1 as desired:

这是我在Excel中快速完成的预期结果，我现在无法通过查询实现。根据需要，数字总和为1：

InvestmentName  AdjWeight
Ac              0.106162299
AG              0.352329321
Ca              0.213255347
DF              0.253437998
Ea              0.028303932
GE              0.046511104

Basically the task here is to divide each entry by the SUM of the all the weights in the first table above.

基本上，这里的任务是将每个条目除以上面第一个表中所有权重的和。

I tried:

我试过了：

SELECT InvestmentName, Weight / SUM(Weight) AS AdjWeight FROM Records WHERE = "..."

and Access said can't divide entries by aggregate. Is there a way to store the SUM(Weight) as a variable somewhere from a query and use it on each individual weight?

和Access表示不能通过汇总来划分条目​​。有没有办法将SUM（Weight）作为变量存储在查询的某个地方，并在每个单独的权重上使用它？

1 个解决方案

#1

SELECT InvestmentName, Weight / (SELECT SUM(Weight) FROM Records) AS AdjWeight FROM Records;

SELECT InvestmentName，Weight /（SELECT SUM（Weight）FROM Records）AS AdjWeight FROM Records;