Re: A GROUP BY question

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