Chuck Roberts <croberts@gilsongraphics.com> writes:
> SELECT j.armasterid, j.ccdescription, j.ccpromisedate, j.ccmasterid,
> sum(jc.jcactcost) as sumactcost FROM job j join jobcost jc ON
> (j.ccmasterid = jc.ccmasterid) WHERE (j.ccpromisedate BETWEEN '2014-07-01'
> AND '2014-07-05') AND (jc.jcactcost > 0) group by j.ccmasterid ORDER BY
> j.ccmasterid ;
> Error is: "ERROR: column "j.armasterid" must appear in the GROUP BY clause
> or be used in an aggregate function at character 8"
Is ccmasterid the primary key of "job"? If it's not, this query isn't
well defined: there wouldn't be a unique value of the other "job" columns
for any particular group.
If it is the pkey, more recent versions than PG 8.4 would have accepted
the query as-is, but on 8.4 you'll need a hack. You can either GROUP BY
all the additional columns, eg
group by j.ccmasterid, j.armasterid, j.ccdescription, j.ccpromisedate
or you can use a dummy aggregation for each other column, eg
SELECT max(j.armasterid), max(j.ccdescription), ...
Neither of these hacks will change your results if the query is
well-defined.
If ccmasterid isn't unique, then you need to rethink what you're
grouping by so that the query makes sense.
regards, tom lane
