All Forums Database
amerid 9 posts Joined 02/13
23 Jun 2015
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?
 

yuvaevergreen 93 posts Joined 07/09
26 Jun 2015
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

 

You must sign in to leave a comment.