Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
DATABASES, TABLES AND QUERIES >>  WHY WON'T MY "GROUP BY" QUERY WORK?

  Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy



In your VFP 6 application you have a simple query over one table:
SELECT *, SUM(nAmount) AS nTotal ;
   FROM MyTable ;
   GROUP BY FLD1, FLD2 ;
   INTO CURSOR MyCursor

In VFP 6, this query works like a charm, but as soon as you attempt to run it in VFP 8 or VFP 9, you get an error message. "SQL: GROUP BY clause is missing or invalid"

Here is why you get the error: Lets assume that in your table you have the following data:
FLD1   FLD2   FLD3   nAmount
 1      1      1      1
 1      1      2      1

In VFP 6, you would get a single record as the result. It would have FLD1 = 1, FLD2 = 1 and nTotal = 2. What would it show as FLD3? If you run it enough times, your query will return different results for FLD3. Sometimes it will be 1, and sometimes it will be 2. The reason is that VFP does not know which record to pick the FLD3 result from.

In VFP 8, the syntax rules for the GROUP BY clause changed. Now, in your field list, you are only permitted to use aggregate fields (in the example above, "SUM(nAmount) AS nTotal") or fields that are included in the GROUP BY clause. To correct this query you must change it to:
SELECT FLD1, FLD2, SUM(nAmount) AS nTotal ;
   FROM MyTable ;
   GROUP BY FLD1, FLD2 ;
   INTO CURSOR MyCursor

"Woe is me" you say! "I have hundreds of queries in my app and now I have to modify most of them!" Not a problem. VFP allows you to set the query engine you wish to use:
SET ENGINEBEHAVIOR 70

Now, before you go thinking that all is well, you still have to modify your queries. SET ENGINEBEHAVIOR is simply a stop-gap way to keep your apps running. VFP made this change in order to make the SELECT command more ANSI compliant, and your code needs to reflect that. If you ever hope to upsize to a SQL backend, you will have to make these modifications anyway as SQL applies these same rules.

SET ENGINEBEHAVIOR to 70 and it will give you the time you need to go through your code correcting these queries.

Hope this helps.

FEEDBACK

Boudewijn Lutgerink @ 2/12/2007 10:34:17 AM
Great Faq Ken, my compliments
Boudewijn

Ken Murphy @ 2/12/2007 1:09:59 PM
Thanks Boudewijn

Ronan Masangcay @ 3/13/2007 4:04:03 AM
Right on the money Ken! Thanks =)

Ken Murphy @ 3/13/2007 12:13:49 PM
Thanks Ronan

Barbara Peisch @ 4/5/2007 9:34:11 PM
You may want to add a change made in VFP 9--that agreegate functions may return a NULL field instead of a _tally of 0! For example:

SELECT FLD1, FLD2, SUM(nAmount) AS nTotal ;
FROM MyTable ;
WHERE SomeCondition ;
GROUP BY FLD1, FLD2 ;
INTO CURSOR MyCursor

If SomeCondition does not evaluate to .T. for any records, _tally will be 1 but nTotal will be NULL.

Ken Murphy @ 4/5/2007 11:45:10 PM
Thanks Barbara, I didn't know that.

Patrick McGreevy @ 11/6/2007 7:50:38 PM
Perfect timing, Ken! We are transitioning to SQL right now.
Thanks!

Ulhas Amrite @ 1/11/2008 6:07:07 AM
Very interesting and useful to me! Thanks Ken

Stoycho Stoychev @ 1/21/2008 3:41:17 PM
Only if I had read the FAQ..... I wouldn't waste two days to discover the wheel :)
Short, simple and clear. Good job Ken.

Kash @ 4/7/2008 12:36:24 PM
Thanks for the above.

I have another problem where Foxpro will not allow me to set enginebehaviour to 70. I am using Foxpro 9, and the options it gives it:

Report Engine Behavior:

80 (backward compatible)
90 (object assisted)

I am not getting an option to set this to 70. Please can someone help.

Thanks

Luis Daniel Franco Perez @ 6/28/2012 4:18:15 PM
en visual fox 6 no hay problema con el grou by pero en 7 8 y 9 cambia

EL SELECT * EN GROUP BY NO ES VALIDO AHORA SE DEBEN DEFINIR TODOS LOS CAMPOS QUE PERTENECEN AL * Y LOS QUE NO ESTAN EN EL GROUP BY DEFINIRLOS CON MAX( NOMBRE_CAMPO ) POR EJEMPLO

SELECT MAX( CAMPO1 ), MAX( CAMPO2 ), CAMPO3, CAMPO4
FROM TABLA1
GROUP BY CAMPO3, CAMPO4



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: