beginner question on join

[kohlweyer]

I have a table "Projects" and a table "Report". Projects has a field "plan" and Report a field "actual".
For each project many reports can be made by different employees.

I want to create a query where I can see the sum of the actual of all reports on all projects a employee made together with the sum of the plan of the projects and group it by each employee.

SELECT [Report].[employee], Sum([Report].[actual]), Sum([Project].[plan])
FROM Project INNER JOIN Report ON [Project].[no]=[Report].[projectno]
GROUP BY [Report].[employee];

example:
project p1 has plan = 5
project p2 has plan = 7
employee e1 makes report on p1, actual = 3
employee e1 makes report on p1, actual = 1
employee e1 makes report on p2, actual = 5

When I run the query the sum of the actual is correct (9) but the sum of the plan is 17 instead of 12

How can I change the query to meet my demands? Thank you very much.

 

[r937]

does this produce correct results --

select P.plan
     , R.employee
     , Sum(R.actual) 
  from Project as P
inner 
  join Report as R
    on P.no = R.projectno
group 
    by P.plan
     , R.employee

this is not necessarily yet what you want but is the data actually correct?

r937.com | rudy.ca

 

[kohlweyer]

This is not quite what I intend, because if an employee has reported on more than one projet, I get one output line per project. But I would like to get it cululated, that means one line per employee.

 

[r937]

well, assuming that the data from that query is correct, you then summarize it like this --

select employee
     , count(*) as plans
     , sum(actual) as total_actual
  from (
       select P.plan
            , R.employee
            , Sum(R.actual) 
         from Project as P
       inner 
         join Report as R
           on P.no = R.projectno
       group 
           by P.plan
            , R.employee
       ) as Q
group
    by employee

r937.com | rudy.ca

 

[kohlweyer]

Thank you very much for your assistance, but I still don't get it. When I execute the query I have to enter a value for "actual" in line 3. I also tried R.actual and Report.actual, same result.
Sorry for this probably stupid question. Problem is that I don't know how this "select in select" works.

 

[r937]

okay, my fault, i messed it up by forgetting the alias

select employee
     , count(*) as plans
     , sum(actual) as total_actual
  from (
       select P.plan
            , R.employee
            , Sum(R.actual) [b]as actual[/b]
         from Project as P
       inner 
         join Report as R
           on P.no = R.projectno
       group 
           by P.plan
            , R.employee
       ) as Q
group
    by employee

r937.com | rudy.ca

 

[kohlweyer]

Thank you again for your assistance and patience, I apprecitate it very much.
Still though this query does not do what I intend, because now it displays the number of projects that the employee has made reports for. But I would like to see the value of "Project.plan". I tried to add it to the select statement, but then I also have to enter it in the Group By clause and then I don't get one output-line per employee.

 

[r937]

if you onlyu want one line per employee, what do you want it to look like if the employee has more than one plan? you cannot show Project.plan in that case

r937.com | rudy.ca

 

[kohlweyer]

ok, I could figure it out myself:

select employee
, sum(plan) as plans
, sum(actual) as total_actual
from (
select P.plan as plan
, R.employee
, Sum(R.actual) as actual
from Project as P
inner
join Report as R
on P.no = R.projectno
group
by P.plan
, R.employee
) as Q
group
by employee

Thanks again, you were a very big help!