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]

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.

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.

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.

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.