Group By All

[tommo6210]

Hi,

I've have a query with about 15 columns in it, one of them being a count() function.

Obviously as I'm using an aggregate function I have to use the GROUP BY statement.

My question is, is there a 'shortcut' way of saying group by *all* columns except the one that I'm aggregating, to save having to list the 14 columns explicitly in the GROUP BY statement?

i.e. something like GROUP BY * ...which I know doesn't work, cos I've tried it!!

Cheers,
Chris.

 

[r937]

there is no "all" option for GROUP BY

are the 14 columns all from the same table? is one of them the primary key?

if so (and only under those circumstances), then you can GROUP BY the pk only

see Debunking GROUP BY myths





r937.com | rudy.ca

 

[rac2]

r937,

Thanks for that link. I really wanted to know more about GROUP BY. Wonderful that Mr Bouman would explore the matter deeply.

But I must confess he lost me along about the "So why would I do that?" paragraph. My head began to spin.

MySQL is wack to allow items in the SELECT list which are not in the GROUP BY list; as the MySQL documentation cautions, the result is undefined. So dont do it.

As to the unhelpful error message provided by Oracle, I can only smile. Error messages are often unhelpful or misleading as I am sure we all discovered on day 2 in our careers as programmers.

If a column is functionally dependent on a column in the GROUP BY list you can include it in the SELECT list and it will have no effect on the result; if it is not, then dont do it.

So Mr Bouman failed to convince me that the simple rule, "columns referenced in the SELECT list of a query to also appear in the GROUP BY clause, unless these columns appear exclusively in an aggregated expression" is a myth.

If you need to get some pesky details about the categories in the GROUP BY query, then you can include columns in the GROUP BY and the SELECT lists which are candidate keys and JOIN the GROUP BY to the detail table as a subquery or view.

What am I missing?

 

[r937]

i'm sorry, rac2, i'm not sure i can do any better that roland did in the article

read it slowly, it does make sense

the mysql docs warn you that the following query can produce unpredictable results --

select t1.pk
, t2.somecol
from ...
group by t1.pk

the value for t2.somecol not being dependent on t1.pk, therefore it could be any value in the group

on the other hand this ---

select t1.pk
, t1.someothercol
from ...
group by t1.pk

is perfectly safe, since the groups are determined by the t1.pk values, therefore for each t1.pk value, the value of t1.someothercol is determined

it is similar to the concept of non-key attribute being functionally dependent on the pk when you do normalization

anyhow the article does make sense, maybe on the 2nd or 3rd time through...




r937.com | rudy.ca