AVG 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

SQL Server Development (2000)

AVG Function

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-06-25 : 18:51:28

I have the following query. I want a returned row at the bottom to have an average for the ship_wt field.

select *, avg(ship_wt) from dbo.ship_FTM where part like '9200%' and desc1 like '%%' and status like 'active'

I get this error:

Server: Msg 8118, Level 16, State 1, Line 1
Column 'dbo.ship_FTM.part' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'dbo.ship_FTM.desc1' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'dbo.ship_FTM.desc2' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'dbo.ship_FTM.draw' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'dbo.ship_FTM.status' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'dbo.ship_FTM.ship_wt' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'dbo.ship_FTM.ship_wt_um' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'dbo.ship_FTM.net_wt' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'dbo.ship_FTM.net_wt_um' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

How do I incorporate the other fields into my query?

Thanks!

Lane

MakeYourDaddyProud

184 Posts

Posted - 2002-06-25 : 19:03:37

If you take out the '*', you should be fine with

select avg(ship_wt) from dbo.ship_FTM where part like '9200%' and desc1 like '%%' and status like 'active'

But I am a little concerned about your use of pattern match, in particular

quote:
desc1 like '%%' and status like 'active'

desc1 will match all columns regardless of content and 'status' will only predicate match 'active' since there are no expressions. the status condition can be rewritten as

status = 'active'

Is that what you wanted? Also the SQL will only return 1 line, you might want to group by other columns, and these would need to be reflected in the SELECT list accordingly.

Dan

www.danielsmall.com IT rental & Factoring

<<monet makes money>>

lane0618
Posting Yak Master

134 Posts

Posted - 2002-06-25 : 19:12:58

That does it!

Thanks,
Lane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources