Re: A GROUP BY question

> On 13 Aug 2019, at 13:10, stan <stanb(at)panix(dot)com> wrote:
>
> select
> project.proj_no ,

Removed columns that get in the way of your desired result. You can’t have both details and the sum over them in a meaningful way.

> 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

(break)

> inner join employee on
> rate.employee_key = employee.employee_key
> inner join work_type on
> rate.work_type_key = work_type.work_type_key

These are now probably redundant, you don’t need them unless they filter your results.

> inner join project on
> project.project_key = task_instance.project_key

And this JOIN could be dropped if project_key and proj_no weren’t different fields. If both are unique in project, you could drop one of them and keep the same functionality with fewer joins. That said, in the “war” between surrogate and natural keys I’m on the natural keys side. Clearly, not everyone agrees on that.

> GROUP BY
> project.project_key ,

Same columns removed here too.

> ORDER BY
> project.proj_no
> ;

That should give you the total cost for each project.

You could get the same result repeated per employee and per work type as you tried originally, by putting the above revised query as a subquery and joining that back into the full query in the place of your project-related tables (add the project_key so you have something to join against).

The repeated sum risks getting multiplied in the final output though, especially if unaware people will be putting the results in an Excel sheet or something. From experience, that either results in people reporting the wrong financial results (several orders too high) or blaming your query.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.