473,381 Members | 1,504 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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 2044
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

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

Similar topics

11
by: David | last post by:
I am learning plsql. I would like to run a stored procedure to calculate my bank account value by predicted 10% annual growth rate. Below is my plsql that is having problems. Your help is highly...
53
by: Cardman | last post by:
Greetings, I am trying to solve a problem that has been inflicting my self created Order Forms for a long time, where the problem is that as I cannot reproduce this error myself, then it is...
1
by: sg_s123 | last post by:
============================================================================ 02-Feb-04 03-Feb-04 Staff Staff 0800hr- 1300hr- 1700hr- 1900hr- 0800hr- 1300hr- 1700hr- 1900hr- Number...
2
by: Hugo L. | last post by:
I really don't know how to calculate the median. Can anybody help me?
1
by: Guido | last post by:
How to cope with query output which provides all possible values based on a one to many relationship between two tables. I'm only interested in one answer per primary key in the 'one' table. I've...
6
by: Herrcho | last post by:
in K&R Chapter 6.3 it mentions two methods to calculate NKEYS. and points out the first one which is to terminate the list of initializers with a null pointer, then loop along keytab until the...
11
by: Thomas | last post by:
Hi, I'm pretty new to the programming world. I got stuck in the following problem. Please guide me about it. Well I'm trying to get the value of (128^100)^2 I've used long double type but to no...
1
by: whnkee | last post by:
I need to audit the freight bills charged by freight company which has over 20,000 consignment per week. The basic charge is base on distance and weight, something like this: SYD-MEL <1kg $6...
5
FishVal
by: FishVal | last post by:
IMHO, the following is not a how-to-do instruction to solve a particular problem but more a concept-proof stuff demonstrating possibilities of SQL. So, let us say the problem is to calculate...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

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.