GROUP BY in BOL!

16 Jul 2005 5:05 AM

Arpan

In BOL, under the sub-topic 'GROUP BY Fundamentals', it is stated that

---------------------------------------------
You can group by an expression as long as it does not include aggregate
functions.
---------------------------------------------

If I am not mistaken, the above sentence can be interpreted as

---------------------------------------------
You can't group by an expression as long as it is included in an
aggregate function.
---------------------------------------------

But the following query is very much valid:

---------------------------------------------
USE pubs
SELECT pub_id,SUM(price) AS TotalPrice FROM Titles GROUP BY pub_id,
price
---------------------------------------------

So isn't the sentence given in BOL errorneous or have I misinterpreted
it?

Thanks,

Arpan

16 Jul 2005 5:28 AM

Jens Süßmeyer

"group by an expression"

What you did is that you hrouped by a column not an expression, the
expression would be in your case sum(price)

A column can be seen as an expression, but an expression is some sort of
virtual column.

HTH, Jens Suessmeyer.

Show quote

"Arpan" <arpan***@hotmail.com> wrote in message
news:1121490318.683070.77860@g14g2000cwa.googlegroups.com...
> In BOL, under the sub-topic 'GROUP BY Fundamentals', it is stated that
>
> ---------------------------------------------
> You can group by an expression as long as it does not include aggregate
> functions.
> ---------------------------------------------
>
> If I am not mistaken, the above sentence can be interpreted as
>
> ---------------------------------------------
> You can't group by an expression as long as it is included in an
> aggregate function.
> ---------------------------------------------
>
> But the following query is very much valid:
>
> ---------------------------------------------
> USE pubs
> SELECT pub_id,SUM(price) AS TotalPrice FROM Titles GROUP BY pub_id,
> price
> ---------------------------------------------
>
> So isn't the sentence given in BOL errorneous or have I misinterpreted
> it?
>
> Thanks,
>
> Arpan
>

16 Jul 2005 5:49 AM

Arpan

I got the point, Jens. What BOL means is you can group by an expression
as long as the EXPRESSION BEING USED IN THE GROUP BY CLAUSE does not
include an aggregate function i.e. you can't do something like this:

----------------------------------------------
SELECT.....FROM.....GROUP BY SUM(ColName/Expr) /*or AVG(ColName/Expr)
or MAX(ColName/Expr) or MIN(ColName/Expr)*/
----------------------------------------------

I misinterpreted it!!

Thanks, Jens :-)

Regards,

Arpan

16 Jul 2005 6:01 AM

Stu

You've misinterpreted it. Not that the BOL is completely error-free,
but in this case your interpretation is off. You are correct that the
query:

SELECT pub_id,SUM(price) AS TotalPrice
FROM Titles
GROUP BY pub_id, price

will run, but it will return spurious results. What won't work is the
following:

SELECT pub_id,SUM(price) AS TotalPrice
FROM Titles
GROUP BY pub_id, MIN(price)

In other words,

You can't include an aggregate function in a group by clause.

HTH,
Stu