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