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
;
The underlined lines are what I added, and I get a syntax error pointing to
the join. This works fine without the added lines.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
