Aggregate Function - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Aggregate Function

Author

Topic

retropost
Starting Member

1 Post

Posted - 2005-03-02 : 18:09:54

Could someone give me some help on inserting the field PRContactAssoc.Role with this script? (Error received is below)
The data is "GROUP BY PRContactAssoc.WBS1, Contacts.ClientID" because these two fields together create the primary key for the PRClientAssoc table. Thanks a LOT! - S

INSERT INTO PRClientAssoc
SELECT
PRContactAssoc.wbs1, Contacts.ClientID, 'N', null, Null, PRContactAssoc.Role, 'N', 'ADMIN', Null, 'ADMIN', Null
From PRContactAssoc
Inner Join
Contacts
on
PRContactAssoc.ContactID = Contacts.ContactID
WHERE
Contacts.FirstName not like 'Office' and
not exists
(select 'x' from PRClientAssoc where
PRContactAssoc.WBS1 = PRClientAssoc.WBS1 and
Contacts.ClientID = PRClientAssoc.ClientID)
GROUP BY PRContactAssoc.WBS1, Contacts.ClientID

Server: Msg 8120, Level 16, State 1, Line 1
Column 'PRContactAssoc.Role' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-02 : 19:11:26

The error message says it all.
either: you include PRContactAssoc.Role in the group by,
or you contain it in an aggregate function, eg. min(PRContactAssoc.Role) as Role

Next question:
in the query, will there be any difference between
a) min(PRContactAssoc.Role)
b) max(PRContactAssoc.Role)
??????
Motivate Your answer.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources