469,323 Members | 1,506 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

How do I calculate something using values from two tables?

abehm
35
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 1884
Delerna
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
  4.  
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
abehm
35
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
abehm
35
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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.