Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 27th, 2003, 12:56 AM
Registered User
 
Join Date: Jun 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default calc col from one table attached to another

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.
 
Old June 27th, 2003, 01:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 27th, 2003, 06:39 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 29th, 2003, 02:15 AM
Registered User
 
Join Date: Jun 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Calc TEXTAREA ROWS & COLS based on Font Ron Howerton Classic ASP Basics 0 March 21st, 2005 11:17 AM
A debugger is already attached lamdog ASP.NET 1.0 and 1.1 Basics 1 March 10th, 2005 10:39 PM
Switching problem between view and dialog attached lmadhavi Visual C++ 0 September 21st, 2004 07:14 AM
Limiting of Calc. Field Output to 2 decimals Vince_Kingston SQL Language 1 June 9th, 2003 01:21 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.