GROUP BY Clause Problem.

I have a query that has a number of fields being returned from two tables (a
company table and a contact table), and I want to make sure that only one
record per company is returned. But when I run the query I get the
following error.
Column 'Contact.name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
(As well as the same error for every other field in the query)
How can you work around this limitation? The DISTINCT clause isn't the
answer, because I could get multiple records with the same company but
different contacts at that company. I also want to try to avoid subqueries.
Thanks!!!
BobIf you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don=B4t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Here is the Query, I am still getting the same error when I add a "token"
min() field.
SELECT Company.company_name, LEFT(Contact.name,20) AS NAME, Contact.state,
LEFT(Company.potential_number_of_users,10) AS USERS, Company.original_date,
Company.if_exported, dbo.displayphone(contact.phone_number_1) AS PHONE,
Contact.address_line_1, Contact.address_line_2, Contact.city, Contact.dear,
Contact.email_address, Contact.last_name, Contact.phone_number_1,
Contact.phone_number_fax, Contact.title, Contact.zip,
LEFT(RTRIM(contact.name)+', ' +company.company_name,50) AS COMPLETED_WITH,
MIN(Company.owner) AS GROUPCLAUSE FROM Company Inner Join Contact ON
Company.record_id = Contact.parent_id WHERE UPPER(Company.status) Like '0
LEAD%' And Company.call_back_date <= GETDATE() And Company.owner Like
'BOB%' GROUP BY Company.company_name ORDER BY Company.original_date DESC
What do you think?
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Oh, do you mean min() on "each" displayed column? I'll give it a try...
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Yes.