I have a table below where I am trying to sum the order_value grouping by the unique order number. However, the queries I've tried are returning the entire sum. Expected return is: 34.24 but actual return is 50.37. How do I get the correct return value?

My query:

SELECT order_number, 
       SUM(order_value) 
FROM customer_response 
GROUP BY order_number ;

I've also tried:

SELECT SUM(order_value) 
FROM customer_response 
GROUP BY order_number, order_value

SELECT SUM(order_value) 
FROM customer_response 
GROUP BY order_number

This seems like it should be simple but I'm just not sure where I'm going wrong.

CodePudding user response：

Very similar to your query but first extract only distinct rows for order_number.

SELECT order_number, SUM(order_value)
FROM
(
 select distinct on (order_number) *
 from customer_response
) t 
GROUP BY order_number;

If you need to get the overall sum for all orders then remove the grouping.

SELECT SUM(order_value)
FROM
(
 select distinct on (order_number) *
 from customer_response
) t;

SQL Fiddle

Edit
Actually since only one row is left per order_number summing and grouping is meaningless. So the first query becomes simply

 select distinct on (order_number) *
 from customer_response;

CodePudding user response：

You can try this :

SELECT sum(t.order_avg)
  FROM
     ( SELECT avg(order_value) AS order_avg
         FROM customer_response
        GROUP BY order_number
     ) AS t

CodePudding user response：

If you want to get the sum of all distinct Order_Values per Order_Number, you can group by Order_Number to get the sum in each group and then use SUM() window function to get the total:

SELECT DISTINCT SUM(SUM(DISTINCT Order_Value)) OVER () total_value
FROM customer_response t
GROUP BY Order_Number;

See the demo.