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