472,123 Members | 1,402 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,123 software developers and data experts.

How do I calculate something using values from two tables?

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?
Feb 28 '08 #1
3 1932
1,134 Expert 1GB
try this

Expand|Select|Wrap|Line Numbers
  1. SELECT SUM(Hours/EstimatedHours) AS Progress
  2. FROM Production.TimeLog, Config.ProjectConfig
  3. 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.
Feb 28 '08 #2
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
Feb 29 '08 #3
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
Feb 29 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Hugo L. | last post: by
11 posts views Thread by Thomas | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.