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

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

On 28/01/15 09:33, Chuck Roberts wrote:
>
> Postgresql 8.4 on a Linux box.
>
> Table: Job, with ccmasterid (which is job number), and some other
> fields I need to show.
>
> Table: Jobcost with ccmasterid (job number) and multiple records for
> each record in job. These are multiple records for costs associated
> with each job.
>
> I need to show each job and the sum of costs for each job, along with
> other fields for each job. So this query sums costs by job, but does
> not show the extra columns I need.
>
> SELECT 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 ;
>
> This query shows the other columns I need to show but I get an error.
>
> 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"
>
> So, is there a one statement way to show extra columns AND sum my job
> costs by job?
>
> Thank you! I'm trying to learn as I go and I could not find what I
> wanted via Google.
>
> *Chuck Roberts*| Gilson
> 2000 Oak Industrial Drive NE, Grand Rapids, MI 49505
> d:616.459.4539 ext. 1228 |c: n/a |e:croberts@gilsongraphics.com
> <mailto:croberts@gilsongraphics.com>
>
It is a good idea to quote the full version, as bug fixes and some
sometimes additional features are added with within minor version
upgrades. The latest 8.4 is 8.4.22 which came out in July 2014:
http://www.postgresql.org/ftp/source/v8.4.22

I strongly suggest that you upgrade to a series 9 version, 9.4 was
released in December 2014 and 9.4.1 should be out within a month.
Because as well as numermous bug fixes and performance enhancements,
there are a lot of extra features and documentation improvements.


Cheers,
Gavin

Le 27/01/2015 à 21:33, Chuck Roberts écrivait :

> Table: Job, with ccmasterid (which is job number), and some other fields I
> need to show.
>
> Table: Jobcost with ccmasterid (job number) and multiple records for each
> record in job. These are multiple records for costs associated with each
> job.
>
> I need to show each job and the sum of costs for each job, along with other
> fields for each job. So this query sums costs by job, but does not show the
> extra columns I need.
>
> SELECT 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 ;
>
> This query shows the other columns I need to show but I get an error.
>
> 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"
>
> So, is there a one statement way to show extra columns AND sum my job costs
> by job?

Yes: <http://www.postgresql.org/docs/8.4/static/tutorial-window.html>

> Thank you! I'm trying to learn as I go and I could not find what I wanted
> via Google.

Start here: <http://www.postgresql.org/docs/8.4/static/tutorial.html>

(and, as others said, upgrade your postgresql server)

--
Benoit Izac