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

From: Chuck Roberts <croberts(at)gilsongraphics(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Need to show other columns for job table along with summing job cost
Date: 2015-01-27 20:33:58
Message-ID: 83bb41971f5b62ff87df0c348c4bc8f7@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Gavin Flower 2015-01-27 20:50:57 Re: Need to show other columns for job table along with summing job cost
Previous Message Ziggy Skalski 2015-01-27 15:55:37 Re: Looking for a book on pgsql