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

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