Eric,
I got the quotes down pretty well now after reading a lot of posts (again) and testing yesterday. Thanks.
Rob,
Well, that sure worked. I used option to include all of the fields in the group by. Doesn't make sense, but it does work.
But, why doesn't the sum(RPREFA) work? If you could point me to the right manual, I'd appreciate it.
Here is an example from the manual that doesn't even 'sum' but the result they show does:
Example of report breaks in Query Management
The following SQL statement causes the report to be sorted using the values in the JOB column.
SELECT * FROM STAFF ORDER BYJOB
Their results
ID NAME DEPT JOB YEARS SALARY
------- --------- ------- ----- ------- -----------
100 Plotz 42 mgr 7 18,352.80
-----------
* 18,352.80
90 Koonitz 42 sales 6 18,001.75
-----------
* 18,001.75
Fran Denoncourt
Sr. Programmer/Analyst
Pinal County Treasurer's Office
Florence, AZ 85232
(520) 866-6404
Receipt of this message does not grant you permission to send me
Unsolicited Commercial Email
<rob@xxxxxxxxx> 08/27/2008 12:12 PM >>>
These two lines alone are what's important to this problem
SELECT RPPRCL, RPYEAR, RPBTCH, RPNAME, RPREFA, RPCHCK, RPAPYR
...
GROUP BY RPPRCL
...
Once you do a GROUP BY then the fields in the select have to be one of
three things:
- a function (like SUM, AVG, MIN, MAX...)
- included in the group by.
- omitted from the SELECT.
In other words you have to do one of three things
I - Change your select to
SELECT RPPRCL, MIN(RPYEAR), MAX(RPBTCH), MAX(RPNAME), SUM(RPREFA),
AVG(RPCHCK), SUM(RPAPYR)
- - tossing in a variety of functions as an example
II - Change your group by to include these:
SELECT RPPRCL, RPYEAR, RPBTCH, RPNAME, RPREFA, RPCHCK, sum(RPAPYR)
GROUP BY RPPRCL, RPYEAR, RPBTCH, RPNAME, RPREFA, RPCHCK
III - Drop some fields from the SELECT
SELECT RPPRCL, sum(RPAPYR)
GROUP BY RPPRCL
Rob Berendt
midrange.com
