嘗試計算多列 SQL

我正在嘗試為我所在的類計算 select 語句中的多列。

一切都來自同一張桌子。我想知道那個人支付了多少次，收到了多少次。

每次給予某物時都有一個 ID，但當某人收到某物時，該 ID 將是相同的。我試圖做以下事情，但我不斷讓標量子查詢產生多個元素。

Select Distinct A, Receiverid, Giver, C, D, sum (received amount)
(Select Count(paidid) FROM Table WHERE A ='12345' GROUP BY Giver) as NumberGiven
(Select Count(Receiverid) FROM Table WHERE A ='12345' GROUP BY Receiverid) as NumberReceived
FROM Table
WHERE A = '12345'
Group By A, ReceiverID, Giver, C, D

根據我對問題的理解，您應該能夠使用視窗聚合來解決這個問題：

SELECT  -- no DISTINCT needed, as correctly pointed out by Lennart
 A, ReceiverID, Giver, C, D,
 SUM(ReceivedAmount),
 COUNT(*) OVER (PARTITION BY Giver) as NumberGiven,
 COUNT(*) OVER (PARTITION BY ReceiverID) as NumberReceived
FROM
 YourTable
WHERE
 A = '12345'
GROUP BY
 A, ReceiverID, Giver, C, D
;

您收到錯誤是因為您在 NumberGiven 中按 Giver 進行分組（NumberReceived 也會出現同樣的錯誤）。一個簡單的例子：

x   y
1   1
1   2
2   3

select count(1) from T group by x

將導致：

2  -- the number of rows for group x=1
1  -- the number of rows for group x=2

您的子查詢可能包含多個組，因此您會得到多個計數，這會導致您的錯誤。

由於我不知道您的表是什麼樣的，我只能猜測查詢應該是什麼樣的：

Select A, C, D  -- distinct not necessary because of group by
     , sum(received amount) as received_amount
     , count(case when receiver = A then 1 end) as #received 
     , count(case when Giver = A then 1 end) as #given
NumberReceived
FROM Table
WHERE A = '12345'
Group By A, C, D

引用自：https://dba.stackexchange.com/questions/264993