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

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(at)gilsongraphics(dot)com
> <mailto:croberts(at)gilsongraphics(dot)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