>Dear Experts
>
>Eric goes to bazar and purchase some fruit and vegetable
>
>Transcation is Table1 will be lool like as
>
>--code---name-----qty-----credit-----Debit-----code2
>1401001--Eric-------0---------0--------100--------3201001
>3201001--Apple----10------100----------0--------1401001
>1401001--Eric------0---------0--------200--------3202001
>3202001--Potato-20--------200-----------0--------1401001
>
>Now I want to get purchase summary as
>
>--code---name-----qty---amount1--amount2
>1401001--Eric-----30-----100--------200
>
>amount1 is price of Fruit items, strarts with 3201
>amount1 is price of Vegetable items, strarts with 3202
>
>I wrote this statement but it displays all items
>
>
>group_wise_sale = "csr" + sys(2015)
>select code,name,;
> sum(iif(left(code,4)='3201',cr_amount,0)) as amount_fruit_items,;
> sum(iif(left(code,4)='3202',cr_amount,0)) as amount_non_fruit_items;
> from table1;
> order by code ;
> group by code ;
> into cursor group_wise_sale
>
>select group_wise_sale
>brow
>
Sorry but original design is awfull.
SELECT t1.code,;
SUM(t2.qty) as qty,;
SUM(IIF(INT(t2.code/1000) = 3201,t2.credit,0)) as amt1,;
SUM(IIF(INT(t2.code/1000) = 3202,t2.credit,0)) as amt2 ;
FROM myCursor t1 ;
LEFT JOIN myCursor t2 ON t1.code2 = t2.code ;
WHERE t1.name == 'Eric' ;
GROUP BY t1.code
Cetin