Re: A GROUP BY question

[stan <stanb@xxxxxxxxx>]

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