From: | Benoit Izac <benoit(at)izac(dot)org> |
---|---|
To: | Chuck Roberts <croberts(at)gilsongraphics(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Need to show other columns for job table along with summing job cost |
Date: | 2015-01-28 18:31:04 |
Message-ID: | 87386ulpuf.fsf@izac.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hans Ginzel | 2015-02-02 09:00:28 | Do not output header line in psql |
Previous Message | Chuck Roberts | 2015-01-28 13:48:53 | Need to show other columns for SUM |