I have 1 table that I need to sum a certain column depending on the transno .
such as: transno=1 then sum(colamt1) (for each stationno)
so:

SELECT DISTINCT stationno, sum(colamt1) AS colamt1, 0 AS colamt2 ;
from mytable ;
WHERE tranno=1 ;
INTO CURSOR tmp NOFILTER;
GROUP BY stationno

works fine.

And if tranno=2 then I want to sum(colamt2) (for each station), setting sum of colamt1 to zero
like:

SELECT DISTINCT stationno, 0 AS colamt1, sum(colamt2) AS colamt2 ;
from mytable ;
WHERE tranno=2 ;
INTO CURSOR tmp NOFILTER;
GROUP BY stationno

How do I UNION these two selects? Or JOIN them? to get one table that sums colamt1 where transno=1 (while leaving colamt2 zero) AND sums colamt2 where transno=2 (while leaving colamt1 zero)
Allen