Row Aggregation in SQL-CodePudding

I have below data:

Source  Dest  Fare
jal     del   5000
del     jal   6000
mum     jal   7000
jal     mum   8000

I want the below output:

City     Total_Fare
jal/del  11000
mum/jal  15000

Can anyone suggest query?

CodePudding user response：

I'd use least and greatest to create source-dest pairs, and then group on them and sum the fare:

SELECT   LEAST(source, dest) || '/' || GREATEST(source, dest) AS city, 
         SUM(fare) AS total_fare
FROM     mytable
GROUP BY LEAST(source, dest) || '/' || GREATEST(source, dest)

CodePudding user response：

You want to aggregate by the two involved cities, no matter whether they be source or destination. In order to do that, order them. A simple way to do this is with GREATEST and LEAST:

select
  greatest(source, dest) || '/' || least(source, dest) as cities,
  sum(fare) as total_fare
from mytable
group by greatest(source, dest), least(source, dest)
order by greatest(source, dest), least(source, dest);