What is GROUP BY ALL used for /

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)

What is GROUP BY ALL used for /

Author

Topic

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-13 : 04:44:49

Hello,

The following example uses pubs,

select type, avg(price), count(*) as countofrows
from titles
where royalty = 10
group by all type

The result set includes rows for types that do not have a royalty of 10. Why would somebody want to do that, just to see all the types that did not make it through the where clause (as a reality check) ?

Thanks,
Kevin

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-13 : 05:40:57

You can use it if, say, you want a full list of products but are only interested in the aggregate functions for some of them, as the example shows. I've never needed it myself, but I'm sure there could be a time where it might be useful.

-------
Moo. :)

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-13 : 05:49:30

Interesting...

I wonder how/why a function like that made it into the language.

Kevin

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-13 : 07:29:12

My problem with GROUP BY ALL is that it overrides the WHERE condition, even when the WHERE is in a subquery (but not when it's in a view).


select type, avg(price), count(*) as countofrows
from (
    select type, price
    from titles
    where royalty = 10
  ) as a
group by all type

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-13 : 07:37:00

quote:
Originally posted by Jusvistin

Hello,

The following example uses pubs,

select type, avg(price), count(*) as countofrows
from titles
where royalty = 10
group by all type

Is this query in BOL? If so then I guess it is a mistake. A copy and paste error!

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-13 : 07:37:21

I don't really like it, either. I like more control over exactly how the results are returned, and it isn't really clear always (as Arnold mentions) what the ALL is refering to (and, it is hard to notice -- could be a PAIN for debugging!).

I prefer:


SELECT
  Types.Type, AvgPrice, CountOfRows
FROM
  Types
LEFT OUTER JOIN
 (
  select type, avg(price), count(*) as countofrows
  from titles
  Where royalty = 10
  group by type
  ) a
ON
  Types.Type = a.Type

Assuming, of course, there exists a table of "Types". (hopefully!)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources