>>>Dear all,
>>>I have a table with below data:
>>>

>>>Dcode – Pcode – Payment – Discount
>>>1    -    1    -   10000  - 0
>>>1    -    1    -   40000  - 0
>>>1    -    1    -   20000  - 1000
>>>1    -    2    -   30000  - 0
>>>

>>>I want to write a SQL that make a cursor as below
>>

>>>Dcode – pcode – payment – DisNum – UnDisNum
>>>1    -    1    -   70000  - 1 – 2
>>>1    -    2    -   30000  - 0 -  1
>>>

>>>Here is my SQL

>>>Select Main.DCode, Main.PCode, SUM(Payment), sum(IIF(Disount>0,1,0000000000)) as DisNum,
>> sum(IIF(Disount>0,0000000000,1)) as UnDisNum ;
>>>From Main ;
>>>Group by Main.DCode,Main.PCode ;
>>>Into cursor TempCrs Readwrite

>>>
>>>But I don’t know how I can count num of records that their Discount is empty and num of records that their Discount is not empty.
>
>Thank you very much.
>but why Disount>0,0000000000,1
>can you explain it for me?
>i put only one 0 and it work.
>>>Can I do this in one SQL statement ?
>>
>>See changes inline.

I think 000000000 just to make the resulting field the desired length. I also don't remember if the problem appears in this SQL as you may get truncated results (***) or it's in some other situations, so I just put it as a precaution.