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
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 |