Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

aggregate functions/group by

Status
Not open for further replies.

robman70

Technical User
Joined
Aug 6, 2002
Messages
90
I'm working with mssql 2000 and vb, i have an sql statement:
Code:
select OCity, OState, OZip From Quotes GROUP BY OCity, OState, OZip ORDER BY OCity, OState, OZip
and this works great, the problem is that i need one more column from the table, so i changed the query to
Code:
select QuoteID, OCity, OState, OZip From Quotes GROUP BY OCity, OState, OZip ORDER BY OCity, OState, OZip
but now i get a message saying 'Column "Quotes.QuoteID" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.' Fine, I stuck it in the group clause but now my results are all messed up. How can i get whats in the QuoteID column without messing up the results of the query. Thanks in advance...
 


SELECT statement without any aggregate functions (MAX(), SUM(), MIN(), etc.) and containing a GROUP BY expression actually return distinct records. The GROUP BY expression must contain all the non-aggregate columns contained in the SELECT clause or the GROUP BY expression must match the select list expression exactly.

So, your first statement

Code:
select OCity, OState, OZip From Quotes GROUP BY OCity, OState, OZip ORDER BY OCity, OState, OZip

produces the same result recordset as :

Code:
select distinct OCity, OState, OZip From Quotes 
ORDER BY OCity, OState, OZip

Which, if you added the QuoteID to the SELECT and GROUP BY expression (and I assume the QuoteID value is unique and possibly a Primary Key) then SELECT distinct QuoteID, OCity, OState, OZip From Quotes ORDER BY OCity, OState, OZip will produce a recordset consisting of the entire table.



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach
 
Mark,
Thanks for the reply, as i was reading over your reply i was thinking to myself, my question makes no sense at all, so i checked again, and as it turns out i was looking at the wrong sub, sorry to waste everyone's time, i already have the data i need. see what happens when you ask an overworked sys admin to program :)

thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top