我试图通过此数据获得每个客户/产品/城市组合的加权平均费用。我不需要子产品的细节水平。
我的数据看起来像这样:
+--------+---------+-------+--------------+-------+----------------+
| Client | Product | City | Sub Product | Sales | Fee % of Sales |
+--------+---------+-------+--------------+-------+----------------+
| a | b | b | c | 1000 | 1% |
| a | b | b | d | 2000 | 2% |
| c | c | b | c | 3000 | 3% |
| d | c | b | c | 4000 | 4% |
+--------+---------+-------+--------------+-------+----------------+
我想计算每个客户的加权平均费%&产品组合。即,对于客户'a'
,产品'b'
,城市'b'
:销售的费用为(1,000/3,000)*1% + (2,000/3000 * 2%)
完成此操作后,我将有另一个查询,该查询仅在上一个查询中获取客户,产品,城市销售和新的加权平均字段。我需要另一个查询,因为我将使用结果作为较大查询的一部分。
使用窗口函数会更轻松地完成,但是由于您使用的是MS-Access ...您可以在子程序中计算每个客户端/product/product/city的销售小写,然后带有原始表格的JOIN
:
SELECT
t.client, t.product, t.city, SUM(t.sales * t.fee / t1.sales) res
FROM
mytable t
INNER JOIN (
SELECT client, product, city, SUM(sales) sales
FROM mytable
GROUP BY client, product, city
) t1
ON t1.client = t.client
AND t1.product = t.product
AND t1.city = t.city
GROUP BY t.client, t.product, t.city
此 DB Fiddle上的演示带有示例数据返回:
| client | product | city | res |
| ------ | ------- | ---- | ------------------------------- |
| a | b | b | 0.016666666294137638 |
| c | c | b | 0.029999999329447746 |
| d | c | b | 0.03999999910593033 |
您可以计算总销售额&费用值作为子查询的一部分,然后执行由结果值,例如:
select
q.client,
q.product,
q.city,
q.fee/q.totalsales as weightedfee
from
(
select
t.client,
t.product,
t.city,
sum(t.sales) as totalsales,
sum(t.sales*t.[fee % of sales]) as fee
from yourtable t
group by t.client, t.product, t.city
) q
更改yourtable
以适合您的桌子名称。