我在下面有一個表格，我試圖order_value按唯一訂單號對分組求和。但是，我嘗試過的查詢回傳了全部金額。預期回報是：34.24但實際回報是50.37。如何獲得正確的回傳值？
| 訂單號 | 訂單價值 | ID |
|---|---|---|
| #1005 | 16.03 | 1 |
| #1005 | 16.03 | 2 |
| #1006 | 18.21 | 3 |
我的查詢：
SELECT order_number,
SUM(order_value)
FROM customer_response
GROUP BY order_number ;
我也試過：
SELECT SUM(order_value)
FROM customer_response
GROUP BY order_number, order_value
SELECT SUM(order_value)
FROM customer_response
GROUP BY order_number
這似乎應該很簡單，但我只是不確定我哪里出錯了。
uj5u.com熱心網友回復：
與您的查詢非常相似，但首先只提取不同的行order_number。
SELECT order_number, SUM(order_value)
FROM
(
select distinct on (order_number) *
from customer_response
) t
GROUP BY order_number;
如果您需要獲取所有訂單的總和，請洗掉分組。
SELECT SUM(order_value)
FROM
(
select distinct on (order_number) *
from customer_response
) t;
SQL小提琴
編輯
實際上，因為每次求和只剩下一行order_number，分組是沒有意義的。所以第一個查詢變得簡單
select distinct on (order_number) *
from customer_response;
uj5u.com熱心網友回復：
你可以試試這個：
SELECT sum(t.order_avg)
FROM
( SELECT avg(order_value) AS order_avg
FROM customer_response
GROUP BY order_number
) AS t
uj5u.com熱心網友回復：
如果你想得到所有不同Order_Values per的總和Order_Number，你可以分組Order_Number得到每個組的總和，然后使用SUM()視窗函式得到總數：
SELECT DISTINCT SUM(SUM(DISTINCT Order_Value)) OVER () total_value
FROM customer_response t
GROUP BY Order_Number;
請參閱演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/410433.html
標籤：