>Is there a way to use a subquery as a field expression in VFP5?
>ex:
>select CompanyName, (select sum(cost) from orders where custid = company.custid and cType = 'fruit') as TotalFruit, (select sum(cost) from orders where custid = company.custid and cType = 'beverage') as TotalBeverage from company order by CompanyName
Select custid,sum(cost) as cost from orders into cursor tmp1 where cType = 'fruit' group by custid
Select custid,sum(cost) as cost from orders into cursor tmp2 where cType = 'beverage' group by custid
Select company.custid,companyname,tmp1.cost as fruitcost From company LEFT JOIN tmp1 ON company.id=tmp1.custid Into cursor tmp3
Select companyname,fruitcost,tmp2.cost as bevtcost From tmp3 LEFT JOIN tmp2 ON tmp3.id=tmp2.custid
Edward Pikman
Independent Consultant