通过这种列的总和除以每行的值 [英] Divide the value of each row by the SUM of this column

问题描述

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

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.

$ P $对 - 调整，用量相对于整个桶如下所示。

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

这是所期望的结果，我在Excel中，迅速做了，我无法与查询实现现在。数字之和为1，期望的：

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:

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

基本上这里的任务是将在第一个表中的每个条目由 SUM所有的权重。

我想：

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

和访问说，不能总分裂条目。有没有办法给SUM（重量）的地方从查询存储为一个变量，并使用它在每个人的体重？

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?

推荐答案

SELECT InvestmentName，重量/（SELECT SUM（重量）FROM记录）AS AdjWeight FROM记录;

这篇关于通过这种列的总和除以每行的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！