Hi Stan,
Am Dienstag, 13. August 2019, 13:10:18 CEST schrieb stan:
> 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.
First of all, complex views including many joins, group by, having etc usually tend to show bad performance on large dataset if used for anything else than simple select from ... statement. So at least my personal experience.
> 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 ,
--------------------^
this gives you the product *per line*
> SUM (rate.rate * task_instance.hours)
--------------------^
this gives you *sum of all products over all grouped lines*
> 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
---------------------^
You group by the columns you use in the sum, so you will get no sum at all, but the product *per line* as selected just before the sum
> ORDER BY
> project.proj_no
> ;
You will have to find out if you really need to group by some lines, and take a sum over those lines or need the product (rate.rate * task_instance.hours) per line.
--
Best regards
Jan