Re: Need to show other columns for job table along with summing job cost

Chuck Roberts <croberts(at)gilsongraphics(dot)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