RE: Problem with GROUP BY

2016-02-22 Thread jerry foote
-Original Message- From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Gene Wirchenko Sent: Monday, February 22, 2016 2:08 PM To: ProFox Email List Subject: RE: Problem with GROUP BY At 13:06 2016-02-20, "jerry foote" <jer...@footegroup.com> wrote: >Thanks fo

RE: Problem with GROUP BY

2016-02-22 Thread Gene Wirchenko
At 13:06 2016-02-20, "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

Re: Problem with GROUP BY

2016-02-20 Thread mbsoftwaresolutions
On 2016-02-20 15:34, Jean MAURICE wrote: Le 20/02/2016 21:28, mbsoftwaresoluti...@mbsoftwaresolutions.com a écrit : I've often thought: the order of your GROUPING doesn't matterdoes it? Either way, it's the same distinct combination. No if you have'nt an order by clause : by default the

RE: Problem with GROUP BY

2016-02-20 Thread mbsoftwaresolutions
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

Re: Problem with GROUP BY

2016-02-20 Thread Tracy Pearson
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 wrote: >On 20 February 2016 at 20:34, Jean MAURICE

RE: Problem with GROUP BY

2016-02-20 Thread jerry foote
16 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

Re: Problem with GROUP BY

2016-02-20 Thread Paul Hill
On 20 February 2016 at 20:34, Jean MAURICE wrote: > Le 20/02/2016 21:28, mbsoftwaresoluti...@mbsoftwaresolutions.com a écrit : >> >> I've often thought: the order of your GROUPING doesn't matterdoes it? >> Either way, it's the same distinct combination. > > No if you

Re: Problem with GROUP BY

2016-02-20 Thread Jean MAURICE
Le 20/02/2016 21:28, mbsoftwaresoluti...@mbsoftwaresolutions.com a écrit : I've often thought: the order of your GROUPING doesn't matterdoes 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

RE: Problem with GROUP BY

2016-02-20 Thread mbsoftwaresolutions
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 matterdoes it? Either way, it's the

RE: Problem with GROUP BY

2016-02-20 Thread Tracy Pearson
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 wrote: > >I have adjusted the code as below and still get error

Re: Problem with GROUP BY

2016-02-20 Thread Paul Hill
On 20 February 2016 at 19: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

RE: Problem with GROUP BY

2016-02-20 Thread mbsoftwaresolutions
--Original Message- From: ProFox [mailto:profox-boun...@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,

RE: Problem with GROUP BY

2016-02-20 Thread jerry foote
release_no INTO CURSOR mytotla -Original Message- From: ProFox [mailto:profox-boun...@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 erro

Re: Problem with GROUP BY

2016-02-20 Thread Jean MAURICE
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

Problem with GROUP BY

2016-02-20 Thread jerry foote
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