Sum Distinct Duplicated Values-CodePudding

I have a dataset as below:

customer      buy          profit      
   a        laptop          350       
   a        mobile          350       
   b        laptop case     50       
   c        laptop          200       
   c        mouse           200

It does not matter how many rows the customer has, the profit is already stated in the row (it's already an accumulative sum). For example, the profit of customer a is 350 and the profit of customer c is 200.

I would like to sum uniquely the profit for all the customers so the desire output should be 350 50 200 = 600. However, I need to execute this in one line of code (without doing subquery, nested query or write in a separate CTE).

I tried with Partition By but cannot combine MAX and SUM together. I also tried SUM (DISTINCT) but it does not work either

MAX(profit) OVER (PARTITION BY customer)

If anyone could give me a hint on an approach to tackle this, it would be highly appreciated.

CodePudding user response：

You really should use a subquery here:

SELECT SUM(profit) AS total_profit
FROM (SELECT DISTINCT customer, profit FROM yourTable) t;

By the way, your table design should probably change such that you are not storing the redundant profit per customer across many different records.

CodePudding user response：

You can combine SUM() window function with MAX() aggregate function:

SELECT DISTINCT SUM(MAX(profit)) OVER () total
FROM tablename
GROUP BY customer;

See the demo.

CodePudding user response：

Select sum(distinct profit) as sum from Table