MySQL GROUP BY, and testing the grouped items

Question

I've got a query like this:

select a, b, c, group_concat(d separator ', ')
from t
group by a;

This seems to work just fine. As I understand it (forgive me, I'm a MySQL rookie!), it's returning rows of:

• each unique a value
• for each a value, one b and c value
• also for each a value, all the d values, concatenated into one string

This is what I want, but I also want to check that for each a, the b and c are always the same, for all rows with that a value.

My first thought is to compare:

select count(*) from t group by a, b, c;

with:

select count(*) from t group by a;

and make sure they're equal. But I've not convinced myself that this is correct, and I certainly am not sure there isn't a better way. Is there a SQL/MySQL idiom for this?

Thanks!

Answer 1

The issue with relying on MySQL's Hidden Columns functionality is spelled out in the documentation:

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

Applied to your example, that means that the values for b and c are arbitrary -- the results can't be relied upon to consistently return the same value, and the likelihood of seeing the behavior increases with the number of possible values that b/c can return. So there's no a lot of value to compare to GROUP BY a and GROUP BY a, b, c...