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
