Understanding GROUP BY clause

Question

SELECT a,b from <table_name> GROUP BY a,b,c.

Is the above a valid sql statement?

Answer 1

Not without a table name it is not. If it had a table name it would be valid, but probably not very useful.

Typically one would use GROUP BY clauses in conjunction with some aggregate function (SUM, COUNT, MAX, MIN, etc.) to derive some values related to the grouped fields.

Answer 2

Yes, that query is legal SQL. Whether it's useful SQL is another matter entirely.

The query

select a , b
from foo
group by a,b,c

Does the following:

• groups the rows from the source table into distinct groups, 1 for each unique combination of columns a, b and c.
• Each such group is then collapsed into a single row containing the grouping columns and the values of any required aggregate functions required by the query.
• The resulting result set is then returned to the caller, tossing any unwanted columns (in this case, column c).

Since one of the grouping columns is discarded, the specified query is not guaranteed to be a set of unique rows. It might well contain duplicates. For instance, if group by came up with these groups to be returned:

A  B  C
-  -  -
0  0  0
0  0  1
0  1  0
0  1  1
1  0  0
1  0  1
1  1  0
1  1  1

The results set returned by the query would be

A  B
-  -
0  0
0  0
0  1
0  1
1  0
1  0
1  1
1  1

And so, not necessarily useful.