By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,259 Members | 1,690 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,259 IT Pros & Developers. It's quick & easy.

How do I calculate something using values from two tables?

P: 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
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,134
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

P: 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

P: 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.