A GROUP BY question

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

Responses

Browse pgsql-general by date

  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