Click here to Skip to main content
14,876,187 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;
   

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