Summing two rows in one

Database
Enthusiast

Summing two rows in one

This SQL 

SEL ORIGIN,DESTINATION, COUNT(PAX)  GROUP BY 1,2

provides the output below.

ORIGIN  Destination  Count
WAS LAX 120
LAX LAX 80
LAX BOS 42
BOS MIA 101
MIA BOS 93
BOS LAX 13

But I want to sum up the rows as shown below.

ORIG_DEST Count
WAS-LAX-WAS 200
LAX-BOS-LAX 55
BOS-MIA-BOS 194

Can you please help?

1 REPLY
Enthusiast

Re: Summing two rows in one

SEL T1.ORIGIN,T1.DESTINATION,T1.ORIGIN,T1.COUNT1+T2.COUNT1
  FROM T1,T1 AS T2
 WHERE
 T1.ORIGIN=T2.DESTINATION AND
 T1.DESTINATION=T2.ORIGIN
 QUALIFY ROW_NUMBER() OVER(PARTITION BY T1.COUNT1+T2.COUNT1 ORDER BY T1.ORIGIN DESC)=1