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

mapidea
Posting Yak Master

124 Posts

Posted - 2009-02-04 : 06:17:03

I have a simple query

Select
count(p2.retailprice),
p2.retailprice
from
Products p2
inner join products p1 on p1.retailprice <= p2.retailprice

It is throwing an error
Column 'Products.RetailPrice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

As the error says ==> My question is p2.retailprice is in the aggregate function COUNT so why the error?

It obviously goes away if I add "group by p1.retailprice".

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-04 : 06:45:08

when u use aggregate functions use group by clause

Select
count(p2.retailprice),
p2.retailprice
from
Products p2
inner join products p1 on p1.retailprice <= p2.retailprice
group by p2.retailprice

mapidea
Posting Yak Master

124 Posts

Posted - 2009-02-04 : 06:55:49

Thanks Bklr. Yes I understand that.

But if we interpret what the Sql Server error says.

It says "not contained in either an aggregate function or the GROUP BY clause"

Though it is contained in the aggregate function.

I suppose we need to take it as a rule that if we use aggregate function we need to use "group by".

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-04 : 07:11:23

quote:
Originally posted by mapidea

Thanks Bklr. Yes I understand that.

But if we interpret what the Sql Server error says.

It says "not contained in either an aggregate function or the GROUP BY clause"

Though it is contained in the aggregate function.

I suppose we need to take it as a rule that if we use aggregate function we need to use "group by".

ur welcome

yes if we use any aggregate functions in query and other columns then u have to use group by clause

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:09:27

quote:
Originally posted by bklr

when u use aggregate functions use group by clause

Select
count(p2.retailprice),
p2.retailprice
from
Products p2
inner join products p1 on p1.retailprice <= p2.retailprice
group by p2.retailprice

This will always give 1 as count as you're grouping and counting the same field. i think you meant this


Select 
count(p1.retailprice),
p2.retailprice
from 
Products p2 
inner join products p1 on p1.retailprice <= p2.retailprice
group by p2.retailprice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources