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 |
|
jgillin
Starting Member
1 Post |
 Posted - 2006-01-26 : 08:23:38
|
| Hi, I'm trying to use a correlated subquery within the "SELECT" clause similar to the solution posted on this page:[url]http://www.sqlteam.com/item.asp?ItemID=6398[/url]My problem is that SQL Server returns the following error:Column 'o1.id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.Here's my query: SELECT Count(o1.id) As TotalRegistrations, Sum(o1.opportunitySize) As TotalProgramValue, Count(o1.id) - Sum(Cast(o1.denied as int)) As Approved, Sum(o1.opportunitySize * ABS(Cast(o1.denied as int)-1)) As ApprovedValue, Sum(Cast(o1.denied as int)) As Denied, Avg(o1.opportunitySize) As AverageDealSize, Avg(o1.opportunitySize * ABS(Cast(o1.denied as int)-1)) As AverageAmountApproved, AmountUnder30K = (SELECT Count(*) FROM tbl_opportunity o2 where o2.id = o1.id and o2.opportunitySize < 30000) FROM tbl_opportunity o1 WHERE o1.reviewed IS NOT NULL AND o1.added BETWEEN '01/02/2006' AND DateAdd(day,1,'01/26/2006') AND o1.partnerName = 'SomeCompany' AND o1.managerID = 535 Basically, the reason I'm using the correlated subquery in the select statement is that that particular column has an "additional" constraint that the other columns don't have (specifically that the opportunitySize column is less than 30000), but it should also have the WHERE clause constraint of the outer query.Does anyone have any thoughts or suggestions on this?Thanks for any help.Jeff |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-26 : 09:16:37
|
| If u use aggregate functions, and display some other fields as well, u need to group by the other fields in concern.eg1. If u want to show how many registrations per each day, u have to group by that date fieldeg2. If u want to show how many registrations per each day and for each type, u have to group by that date field and type field read in BOL about Group By & Aggregate functions (as Count, Sum ...) |
 |
|
|
|
|
|
|
|
Correlated Subquery as Column