Click here to Skip to main content
15,896,526 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a mysql data  table with 3 columns like below:
Item1  Item2  Value

Apple   Orange  3

Apple  Banana  2

Mango  Apple  2

Banana  Apple  6

Apple   Mango   5

Orange  Banana   4

Orange   Apple   5
I want to remove the duplicate rows based on the combinations of two columns Item1 & Item2 and show only one in any order and want to add their values as a result, so as the final output in my table box  can be:
Item1   Item2   Value

Apple   Orange   8

Apple   Banana   8

Mango   Apple   7

Orange   Banana   4
Thank you for any help or attention !  or at least  can any one  answer me if its even possible to do that !! 

What I have tried:

SQL
select T1.item1 ,T1.Item2, sum(T1.Value)  from MyDB as T1 join MyDB as T2 
on T1.item1 = T2.item2 group by item1,item2 ;
Posted
Updated 18-Dec-20 2:47am
v2

1 solution

This is my solution

SELECT LEAST(Item1, Item2) AS Item1,
       GREATEST(Item1, Item2) AS Item2,
       SUM(Value) AS Value
FROM yourTable
GROUP BY 1, 2;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900