From: | stan <stanb(at)panix(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: A GROUP BY question |
Date: | 2019-08-13 11:10:18 |
Message-ID: | 20190813111018.GA19289@panix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Aug 13, 2019 at 05:54:03AM -0400, stan wrote:
> I am trying to write, what is for me, a fairly complex query. It uses JOINS,
> and also GROUP BY. I have this working with the exception of adding the
> GROUP BY clause.
>
> Is there some reason I cannot add a GROUP BY function to a JOIN?
>
> Here is what I have:
>
>
> CREATE OR REPLACE view tasks_view as
> select
> project.proj_no ,
> employee.first_name ,
> employee.last_name ,
> employee.id ,
> task_instance.hours ,
> work_type.type,
> work_type.descrip,
> rate.rate,
> employee.hourly_rate ,
> rate.rate * task_instance.hours as result ,
> SUM(rate.rate * task_instance.hours)
> ^^^^^^^^^^^^^^
> from
> task_instance
> GROUP BY
> ^^^^^^^^^^^^^^^^^^
> project.project_key
> ^^^^^^^^^^^^^^^^^^^^^^
> join rate on
> rate.employee_key = task_instance.employee_key
> AND
> rate.work_type_key = task_instance.work_type_key
> inner join employee on
> rate.employee_key = employee.employee_key
> inner join work_type on
> rate.work_type_key = work_type.work_type_key
> inner join project on
> project.project_key = task_instance.project_key
> ORDER BY
> project.proj_no ,
> employee.id
> ;
>
Maybe I have a basic misunderstanding. What I am trying to get is a total
cost for each project. This would be calculated by multiplying rate and
hours for each row, on a per project base, and then summing all of th
products of this multiplication.
I did get the following to be accepted from a syntax basis, but it returns
rows with the product for each row, and something in the sum column which
is the same.
DROP view tasks_view ;
CREATE OR REPLACE view tasks_view as
select
project.proj_no ,
employee.first_name ,
employee.last_name ,
employee.id ,
task_instance.hours ,
work_type.type,
work_type.descrip,
rate.rate,
employee.hourly_rate ,
rate.rate * task_instance.hours as result ,
SUM (rate.rate * task_instance.hours)
from
task_instance
join rate on
rate.employee_key = task_instance.employee_key
AND
rate.work_type_key = task_instance.work_type_key
inner join employee on
rate.employee_key = employee.employee_key
inner join work_type on
rate.work_type_key = work_type.work_type_key
inner join project on
project.project_key = task_instance.project_key
GROUP BY
project.project_key ,
employee.first_name ,
employee.last_name ,
employee.id ,
task_instance.hours ,
work_type.type,
work_type.descrip,
rate.rate,
employee.hourly_rate
ORDER BY
project.proj_no
;
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Rosenstein | 2019-08-13 11:15:24 | Re: Determining table and column access based on query |
Previous Message | Shital A | 2019-08-13 11:08:44 | Re: Fwd: Postgres HA - pacemaker RA do not support auto failback |