On Sun, Jul 27, 2008 at 12:53 AM, Ajoy Khaund <[EMAIL PROTECTED]> wrote: > Hi All, > > I need some advice (maybe not a great day to ask as its weekend for many but > since I am at it) on a query which has a group by. I am getting some fields > from the Product master and the stock qty from productlot table. > > Vfp6 > > > Select a.prodname,a.iunit,a.avgcost as > unitcost,sum(b.balunits),a.productid,a.categoryid,a.convfactor; > from Products a, Productlot b; > where a.productid = b.productid; > and b.balunits > 0; > group by 5; > order by 1 into cursor View1 > > Vfp9 ----------------------------------------------------
Just copy your SELECTED Fields line of your code as your Group by line leaving out all of the aggregate functions, Min, Max, Sum, etc. Select a.prodname,a.iunit,a.avgcost as unitcost,sum(b.balunits),a.productid,a.categoryid,a.convfactor; from Products a, Productlot b; where a.productid = b.productid; and b.balunits > 0; group by a.prodname,a.iunit,a.avgcost a.productid,a.categoryid,a.convfactor ; order by 1 into cursor View1 Now your syntax would work in most of the backends. HTH -- Stephen Russell Sr. Production Systems Programmer Mimeo.com Memphis TN 901.246-0159 _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.