aggregate functions/group by

[robman70]

I'm working with mssql 2000 and vb, i have an sql statement:

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

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...

 

[MarkSweetland]

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

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

produces the same result recordset as :

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

 

[robman70]

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