返回的子查询除以“子查询返回的值超过1个".错误 [英] Divide by Subquery returning 'Subquery returned more than 1 value.' error

问题描述

我有下表，其中有两个销售订单，每个销售订单具有不同的交易次数.

I have the following table in which I have two sales orders and each sales order has a different number of transactions.

<身体>

SaleOrder	交易	Amount
S1	T1	20
S1	T2	20
S2	T1	15
S2	T2	15
S2	T3	15

问题是，我实际上需要按如下方式在事务数量之间平均分配SQL Server表中每笔交易的总销售订单金额:

The problem is I am getting the total sales order amount against each transaction in SQL Server table when in fact they need to be divided equally among the number of transactions like this:

<身体>

SaleOrder	交易	Amount
S1	T1	10
S1	T2	10
S2	T1	5
S2	T2	5
S2	T3	5

我写了以下查询:

SELECT SalesOrder,
     Transaction,
     Max(Amount) / (SELECT COUNT(Transaction) AS Transaction FROM Test_Table GROUP BY SalesOrder)
    FROM Test_Table
    GROUP BY SalesOrder,
        Transaction

但这给了我'子查询返回的值超过1.当子查询跟随 =，！=，<，< =，>，> = 或将子查询用作表达式时，这是不允许的.错误.有人可以解释一下这是什么以及如何纠正此错误吗?

But it is giving me 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.' error. Can someone explain what this is and how to rectify this error?

推荐答案

我不认为您想要 group by ，只是窗口函数:

I don't think you want group by, just window functions:

SELECT SalesOrder, Transaction,
       Amount) / COUNT(*) OVER (PARTITION BY salesorder)
FROM Test_Table;

这篇关于返回的子查询除以“子查询返回的值超过1个".错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！