query one to many tables w/aggregate functions

[sandygo]

I have a table called Projects and another table called Project Details. The Projects table contains the overall hours quoted for each project. The Project Details table allows the user to have multiple entries per project and specify hours worked for each entry.

Here's a simplified sample of each:

Projects Table

Project Hours Quoted Service Analyst
12 20 Reporting Sandy

Here's a sample of Project Details Table

Entry Project Hours Worked
1 12 10
2 12 10

When I create a query to group by service type, group by analyst, sum quoted hours and sum hours worked, I get:

40 hours quoted and 20 hours worked. It is duplicating the quoted hours for every entry in Project Details. What I want is 20 hours quoted and 20 hours worked.

Any help is appreciated.

Thanks

 

[swampBoogie]

Why are you summing hoursQuoted?

select hoursQuoted,service,analyst,sum(hours)
from projects p inner join projectDetails pd
on p.project = pd.project
group by hoursQuoted,service,analyst

 

[sandygo]

Swamp Boogie,

Thanks for you response, still not working.

I tried grouping by Hours quoted. Problem is it groups by the value in Hours Quoted. For instance, I have three projects with a Service Type of Reporting, each has 1 Hours Quoted. When I group by Analyst, by Service Type, the result is 1 hours quoted for the Service Type of Reporting. Should be 3.

The inner join is set up as you specified.