How do I calculate something using values from two tables?

[abehm]

SELECT (SUM(Hours)/EstimatedHours) AS Progress
FROM Production.TimeLog, Config.ProjectConfig
WHERE Production.TimeLog.ProjectConfigID = 1

I'm trying to calculate the progress of a task by adding all the hours from the timelog records and dividing it by the estimated time stored in the config table?
I wrote the procedure above but I keep getting the following error:
'Column 'Config.ProjectConfig.EstimatedHours' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

what am I missing?

[Delerna]

try this

SELECT SUM(Hours/EstimatedHours) AS Progress
FROM Production.TimeLog, Config.ProjectConfig
WHERE Production.TimeLog.ProjectConfigID = 1
 

the error you was getting was because sum was operating on hours alone and estimatedhours was outside of any aggregate function.
When using aggregate queries, every field mentioned in the SELECTion must be aggregated in some way.

[abehm]

i tried that and I still get progress = 0 for some reason

for projectconfigid = 1
if the timelog.hours records have 5, 12 and 10, and the projectconfig.estimatedhours is 36, the progress value should read .75

[abehm]

So I created the following view to get a total sum of all the hours and the projectconfigid.

SELECT ProjectConfigID, SUM(Hours) AS TotalHours
FROM Production.TimeLog
GROUP BY ProjectConfigID

then I rewrote the procedure like this...

SELECT CONVERT(numeric(8,2),(CONVERT(float,th.TotalHours)/CONVERT(float,pc.EstimatedHours))) AS Progress
FROM vwTotalHours th INNER JOIN ProjectConfig pc ON th.ProjectConfigID = pc.ProjectConfigID
WHERE pc.ProjectConfigID = 1

I kept getting 0's because i didn't realize if you divide two integers the answer will be in integer form.
anyways, this is kinda messy but it seems to work now. Thanks for the help