Thank you Sergey.
Just curious, is there a way to do it without the iif statements?
Allen

>You don't need DISTINCT with GROUP BY

SELECT stationno, ;
>	sum(IIF(tranno=1,colamt1, 0000000.00)) AS colamt1, ;
>	sum(IIF(tranno=2,colamt2, 0000000.00)) AS colamt2 ;
>from mytable ;
>GROUP BY stationno ;
>INTO CURSOR tmp NOFILTER
>

>
>>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