calc col from one table attached to another

Jackie · June 27th, 2003, 12:56 AM

I'm trying to calculate a column from one table(Emps) and include it in the output of another table(Projects).

My attempt:

SELECT Projects.*, sum(Emps.Man_Hours) AS 'Man Hours' FROM [Projects]
LEFT OUTER JOIN Emps ON Projects.Record = Emps.Project_Record
GROUP BY Projects.Record

I keep getting an error msg telling me that I can't include all of the Projects fields(Projects.*) because their not included in the aggregate function or the Group By clause.

In other words I need to include all of the info from the Project table and the sum of man hours employee worked on the project from the "Emps" table. The SQL statement works if I use "Project.Record" instead of "Project.*", but that dosn't do me much good since I need all of the fields from the "Project" table.

This will probably seem incredibly easy to you SQL geniuses out there but it has me currently stumped. Any help would be greatly appreciated.

David Cameron · June 27th, 2003, 01:08 AM

You need to specify each column that you select that is not part of an aggragate in the group by clause. In other words, every column from Projects needs to be listed in the GROUP BY clause. BTW as a general rule it is better not to use * for a number of reasons.
1. Can break client apps. If the table structure changes the client app may not work if it is specifying columns by their order rather then by name.
2. Less clear to read. You aren't specifying exactly which columns you need
3. Slower performance (probably marginal). The server needs to find out what columns there are in the projects table.

HTH

regards
David Cameron

Jeff Mason · June 27th, 2003, 06:39 AM

I believe a correlated subquery will do what you want:

Code:

SELECT Projects.*, 
    (SELECT SUM(Emps.Man_Hours) FROM Emps 
        WHERE Emps.Project_Record = Projects.Record) AS 'Man Hours' 
ORDER BY Projects.Record;

but without table DDL I'm not sure of the exact relationship between the two tables, so I can't guarantee this. :)

Heed what David says about SELECTing *. It's handy to use in a discussion forum like this to signify "here's some columns", but it's really not a good idea in production code.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jackie · June 29th, 2003, 02:15 AM

Thanks to you both for the info and the advice. I'm "Heeding" and modifying my SELECT and GROUP BY to specific col names.