Index
2016-02-20 13:29 – jerry foote : Problem with GROUP BY
2016-02-20 13:38 – Jean MAURICE : Re: Problem with GROUP BY
2016-02-20 13:53 – jerry foote : RE: Problem with GROUP BY
2016-02-20 14:18 – Paul Hill : Re: Problem with GROUP BY
2016-02-20 14:20 – mbsoftwaresolutions@mbsoftwaresolutions.com: RE: Problem with GROUP BY
2016-02-20 14:24 – Tracy Pearson : RE: Problem with GROUP BY
2016-02-20 14:28 – mbsoftwaresolutions@mbsoftwaresolutions.com: RE: Problem with GROUP BY
2016-02-20 14:34 – Jean MAURICE : Re: Problem with GROUP BY
2016-02-20 14:47 – Paul Hill : Re: Problem with GROUP BY
2016-02-20 15:06 – jerry foote : RE: Problem with GROUP BY
Back to top
Problem with GROUP BY

Author: jerry foote

Posted: 2016-02-20 13:29:57   Link

When I try to execute the code below i get error message group by error

SELECT manifest_n, bill_of_la, release_no, frm_acct, source, source_id,

sum(units) as totat FROM SERVICE WHERE manifest_n=2512 AND

bill_of_la="158401" GROUP BY service.release_no INTO CURSOR mytotla ORDER

BYrelease_no

If anyone can spot the problem I would appreciate a heads up

Thanks Jerry

--- StripMime Report -- processed MIME parts ---

multipart/alternative

text/plain (text body -- kept)

text/html

---

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/7790C2CAC28449D68BFEE6023F82B6B9@jerryfootePC

** 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.

©2016 jerry foote
Back to top
Re: Problem with GROUP BY

Author: Jean MAURICE

Posted: 2016-02-20 13:38:25   Link

1) if you work with VFP9, try SET ENGINEBEHAVIOR 70before the SELECT. If it

solves the problem you'll have to change the command. Can you try it ?

2) you have no alias in the field's names so do not write one in the group by

clause so it's "group by release_no"

3) I am not sure 100% but if you have a group by it's should be automatically

ordered without the need of the order by clause.

The Foxil

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/56C8C0B1.3060706@wanadoo.fr

** 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.

©2016 Jean MAURICE
Back to top
RE: Problem with GROUP BY

Author: jerry foote

Posted: 2016-02-20 13:53:12   Link

I have adjusted the code as below and still get error message GROUP BY

clause is missing or invalid.

I am running vp8

SELECT manifest_n, bill_of_la, release_no, frm_acct, source, source_id,

sum(units) as totat FROM SERVICE WHERE manifest_n=2512 AND

bill_of_la="158401" GROUP BY release_no INTO CURSOR mytotla

-----Original Message-----

From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of jerry foote

Sent: Saturday, February 20, 2016 1:30 PM

To: 'ProFox Email List'

Subject: Problem with GROUP BY

When I try to execute the code below i get error message group by error

SELECT manifest_n, bill_of_la, release_no, frm_acct, source, source_id,

sum(units) as totat FROM SERVICE WHERE manifest_n=2512 AND

bill_of_la="158401" GROUP BY service.release_no INTO CURSOR mytotla ORDER

BYrelease_no

If anyone can spot the problem I would appreciate a heads up

Thanks Jerry

--- StripMime Report -- processed MIME parts --- multipart/alternative

text/plain (text body -- kept)

text/html

---

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/95085968BA4E424AA1AF83A9CB2B3255@jerryfootePC

** 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.

©2016 jerry foote
Back to top
Re: Problem with GROUP BY

Author: Paul Hill

Posted: 2016-02-20 14:18:28   Link

On 20 February 2016 at 19:53, jerry foote <jerryf@footegroup.com> wrote:

>

> I have adjusted the code as below and still get error message GROUP BY

> clause is missing or invalid.

> I am running vp8

>

> SELECT manifest_n, bill_of_la, release_no, frm_acct, source, source_id,

> sum(units) as totat FROM SERVICE WHERE manifest_n=2512 AND

> bill_of_la="158401" GROUP BY release_no INTO CURSOR mytotla

It's because these fields are not in the GROUP BY:

manifest_n, bill_of_la, frm_acct, source, source_id

Technically your SQL is invalid, but older versions of Fox allowed this.

Try:

GROUP BY manifest_n, release_no, bill_of_la, frm_acct, source, source_id

Or use aggregates in the SELECT:

SELECT MAX(release_no), MAX(bill_of_la), release_no, MAX(frm_acct),

MAX(source), MAX(source_id), sum(units) as totat

--

Paul

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/CADwx0+KTETXvzNUOW0JdJ3+H7Uv==pxhy2xOUZNGgaZtK_ghCQ@mail.gmail.com

** 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.

©2016 Paul Hill
Back to top
RE: Problem with GROUP BY

Author: mbsoftwaresolutions@mbsoftwaresolutions.com

Posted: 2016-02-20 14:20:54   Link

Jerry -- you've got to list all non-aggregate fields in your GROUP BY.

Example:

SELECT Field1, Field2, Field3, Field4, sum(Field5)

FROM YourTable

GROUP BY Field1, Field2, Field3, Field4

INTO CURSOR Results

hth,

--Mike

On 2016-02-20 14:53, jerry foote wrote:

> I have adjusted the code as below and still get error message GROUP BY

> clause is missing or invalid.

> I am running vp8

>

> SELECT manifest_n, bill_of_la, release_no, frm_acct, source, source_id,

> sum(units) as totat FROM SERVICE WHERE manifest_n=2512 AND

> bill_of_la="158401" GROUP BY release_no INTO CURSOR mytotla

>

>

> -----Original Message-----

> From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of jerry foote

> Sent: Saturday, February 20, 2016 1:30 PM

> To: 'ProFox Email List'

> Subject: Problem with GROUP BY

>

> When I try to execute the code below i get error message group by error

>

> SELECT manifest_n, bill_of_la, release_no, frm_acct, source, source_id,

> sum(units) as totat FROM SERVICE WHERE manifest_n=2512 AND

> bill_of_la="158401" GROUP BY service.release_no INTO CURSOR mytotla

> ORDER

> BYrelease_no

>

> If anyone can spot the problem I would appreciate a heads up

>

> Thanks Jerry

>

>

>

>

> --- StripMime Report -- processed MIME parts --- multipart/alternative

> text/plain (text body -- kept)

> text/html

> ---

>

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/e27d0c92e224a6f4c8815acf69355fd1@mbsoftwaresolutions.com

** 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.

©2016 mbsoftwaresolutions@mbsoftwaresolutions.com
Back to top
RE: Problem with GROUP BY

Author: Tracy Pearson

Posted: 2016-02-20 14:24:06   Link

When you have an aggregate (the sum), you should group by all the noon-aggregate fields.

You could use the numbers for each field.

Group by 3,1,2,4,5,6

On February 20, 2016 2:53:12 PM EST, jerry foote <jerryf@footegroup.com> wrote:

>

>I have adjusted the code as below and still get error message GROUP BY

>clause is missing or invalid.

>I am running vp8

>

>SELECT manifest_n, bill_of_la, release_no, frm_acct, source, source_id,

>sum(units) as totat FROM SERVICE WHERE manifest_n=2512 AND

>bill_of_la="158401" GROUP BY release_no INTO CURSOR mytotla

>

>

--

Sent from my Android device with K-9 Mail. Please excuse my brevity.

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/99C5DA0E-30B1-4E58-89CD-6D8E68BD1A17@powerchurch.com

** 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.

©2016 Tracy Pearson
Back to top
RE: Problem with GROUP BY

Author: mbsoftwaresolutions@mbsoftwaresolutions.com

Posted: 2016-02-20 14:28:29   Link

On 2016-02-20 15:24, Tracy Pearson wrote:

> When you have an aggregate (the sum), you should group by all the

> noon-aggregate fields.

>

> You could use the numbers for each field.

> Group by 3,1,2,4,5,6

I've often thought: the order of your GROUPING doesn't matter....does

it? Either way, it's the same distinct combination.

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/496748d2917a334fcfd1b572c11d5f17@mbsoftwaresolutions.com

** 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.

©2016 mbsoftwaresolutions@mbsoftwaresolutions.com
Back to top
Re: Problem with GROUP BY

Author: Jean MAURICE

Posted: 2016-02-20 14:34:33   Link

Le 20/02/2016 21:28, mbsoftwaresolutions@mbsoftwaresolutions.com a écrit :

> I've often thought: the order of your GROUPING doesn't matter....does it?

> Either way, it's the same distinct combination.

No if you have'nt an order by clause : by default the order is the one given by

the group by (I hope it's understandable !)

The Foxil

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/56C8CDD9.3070003@wanadoo.fr

** 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.

©2016 Jean MAURICE
Back to top
Re: Problem with GROUP BY

Author: Paul Hill

Posted: 2016-02-20 14:47:15   Link

On 20 February 2016 at 20:34, Jean MAURICE <jsm.maurice@wanadoo.fr> wrote:

> Le 20/02/2016 21:28, mbsoftwaresolutions@mbsoftwaresolutions.com a écrit :

>>

>> I've often thought: the order of your GROUPING doesn't matter....does it?

>> Either way, it's the same distinct combination.

>

> No if you have'nt an order by clause : by default the order is the one given

> by the group by (I hope it's understandable !)

Are you sure? I think the order would be undefined.

Perhaps this is true for Fox but I am sure it is not true for other

SQL variants.

--

Paul

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/CADwx0+LKW+V0s1RqF5-Fcr-_L3Fk2Ft=ep7JZbzrwMse=Wv_tg@mail.gmail.com

** 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.

©2016 Paul Hill
Back to top
RE: Problem with GROUP BY

Author: jerry foote

Posted: 2016-02-20 15:06:22   Link

Thanks for all the feed back on the group by clause.

This what I ended up with

SELECT service.manifest_n, service.bill_of_la, service.release_no,

service.frm_acct, service.source,;

service.source_id, sum(service.units) as totat FROM SERVICE WHERE

manifest_n=2512 ;

AND bill_of_la="158401" GROUP BY service.manifest_n, service.bill_of_la,

service.release_no, service.frm_acct, service.source,;

service.source_id INTO CURSOR mytotla

jerry

-----Original Message-----

From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of jerry foote

Sent: Saturday, February 20, 2016 1:30 PM

To: 'ProFox Email List'

Subject: Problem with GROUP BY

When I try to execute the code below i get error message group by error

SELECT manifest_n, bill_of_la, release_no, frm_acct, source, source_id,

sum(units) as totat FROM SERVICE WHERE manifest_n=2512 AND

bill_of_la="158401" GROUP BY service.release_no INTO CURSOR mytotla ORDER

BYrelease_no

If anyone can spot the problem I would appreciate a heads up

Thanks Jerry

--- StripMime Report -- processed MIME parts --- multipart/alternative

text/plain (text body -- kept)

text/html

---

[excessive quoting removed by server]

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/15C4853E785C4DB19F26065DEFF4FF1C@jerryfootePC

** 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.

©2016 jerry foote