Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlclueless
Starting Member
4 Posts |
 Posted - 2006-01-28 : 14:40:49
|
| I have table that has class names, entries and graduates. Each class can have multiple entries. I want totals of each class. I can do this if I hard code the class name into the WHERE clause. But of course I only get one and it's not very useful. I'm trying to nest a SELECT DISTINCT so I can get sums for each class.SELECT SUM(Entries) as [Total Entries], SUM(Graduates) as [Total Graduates] FROM myTable WHERE (ClassName = 'Math')That is what I have, how do I write this so I get for Math, English, Science without hardcoding the class name. I tried WHERE (ClassName = (SELECT DISTINCT (ClassName) FROM myTable))But that gives me an error saying a subquery can't return more than one value if it follows, <laundrylist> of stuff.Will someone please help the clueless?Thanks! |
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2006-01-28 : 15:05:06
|
| SELECT SUM(Entries) as [Total Entries], SUM(Graduates) as [Total Graduates] FROM myTable WHERE (ClassName IN (SELECT DISTINCT (ClassName) FROM myTable))OR MaybeSELECT SUM(Entries) as [Total Entries], SUM(Graduates) as [Total Graduates] FROM myTable GROUP BY ClassNameTim S |
 |
|
|
sqlclueless
Starting Member
4 Posts |
Posted - 2006-01-28 : 15:11:55
|
| Thank you Tim!!! You are da man! :) The group by works perfect! |
|
|
|
|
|
|
Nested SELECT