From: | stan <stanb(at)panix(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | A GROUP BY question |
Date: | 2019-08-13 09:54:03 |
Message-ID: | 20190813095403.GA2013@panix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Rosenstein | 2019-08-13 10:17:52 | Determining table and column access based on query |
Previous Message | Daulat Ram | 2019-08-13 08:23:11 | ORA-24345: A Truncation or null fetch error occurred -ora2pg |