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 ---
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
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]
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
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]
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
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.
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
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.
My experience with VFP is that the order of the fields in the GROUP BY clause allows me to skip the ORDER BY clause. The grouping is always the same, no matter the order.
On February 20, 2016 3:47:15 PM EST, Paul Hill paulroberthill@gmail.com wrote:
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
On 2016-02-20 15:34, Jean MAURICE 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 !) The Foxil
Ah, but I didn't say anything about the ORDER! I'm just saying the SUMs will be the same.
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]
On 2016-02-20 16:06, jerry foote wrote:
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
Don't forget you can use numbers too, as someone else said.
At 13:06 2016-02-20, "jerry foote" jerryf@footegroup.com wrote:
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
Does that do what you want?
It means: For each combination of service.manifest_n, service.bill_of_la, service.release_no, service.frm_acct, service.source, and service.source_id in the service table, what is the total of service.units?
[snip]
Sincerely,
Gene Wirchenko