>> 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.
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement