>> I have a table that is structured as such.
>>
>> code (c)
>> amount_in (n)
>> amount_out (n)
>> status (l)
>>
>> I'd like to get totals grouped by code and sum amount_in when status=.f.
>> (if status=.t. don't sum amount_in value) and sum amount_out when
>> status=.t. (if status=.f. don't sum amount_out value).
>>
>> I would also like to cnt the total number of amount_in and amount_out
>> records using the same logic I used with sum.
>
>SELECT CODE, ;
> count(iif(status,1,0)) as in_cnt, ;
> count(iif(status,0,1)) as out_cnt, ;
> sum(iif(status,0,amount_in)) as in_sum, ;
> sum(iif(status,amount_out,0)) as out_sum ;
> FROM {yourtable} ;
> ORDER BY 1
> GROUP BY 1
>
>Hopefully this'll do the trick for you.
>
>/Paul
The select statement calculates the sum values corretly but does not calculate the count values correctly. It gives me the total number for all records for both values. It's as if it ignores the iif() within the count clause.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only