In SQL, what does Group By mean without Count(*), or Sum(), Max(), avg(), ..., and what are some uses of it?

Question

In SQL, if we use Group By without Count(*) or Sum(), etc, then the result is as follows:

mysql>  select * from sentGifts;
+--------+------------+--------+------+---------------------+--------+
| sentID | whenSent   | fromID | toID | trytryWhen          | giftID |
+--------+------------+--------+------+---------------------+--------+
|      1 | 2010-04-24 |    123 |  456 | 2010-04-24 01:52:20 |    100 |
|      2 | 2010-04-24 |    123 | 4568 | 2010-04-24 01:56:04 |    100 |
|      3 | 2010-04-24 |    123 | NULL | NULL                |      1 |
|      4 | 2010-04-24 |   NULL |  111 | 2010-04-24 03:10:42 |      2 |
|      5 | 2010-03-03 |     11 |   22 | 2010-03-03 00:00:00 |      6 |
|      6 | 2010-04-24 |     11 |  222 | 2010-04-24 03:54:49 |      6 |
|      7 | 2010-04-24 |      1 |    2 | 2010-04-24 03:58:45 |      6 |
+--------+------------+--------+------+---------------------+--------+
7 rows in set (0.00 sec)


mysql>  select *, count(*) from sentGifts group by whenSent;
+--------+------------+--------+------+---------------------+--------+----------+
| sentID | whenSent   | fromID | toID | trytryWhen          | giftID | count(*) |
+--------+------------+--------+------+---------------------+--------+----------+
|      5 | 2010-03-03 |     11 |   22 | 2010-03-03 00:00:00 |      6 |        1 |
|      1 | 2010-04-24 |    123 |  456 | 2010-04-24 01:52:20 |    100 |        6 |
+--------+------------+--------+------+---------------------+--------+----------+
2 rows in set (0.00 sec)


mysql>  select * from sentGifts group by whenSent;
+--------+------------+--------+------+---------------------+--------+
| sentID | whenSent   | fromID | toID | trytryWhen          | giftID |
+--------+------------+--------+------+---------------------+--------+
|      5 | 2010-03-03 |     11 |   22 | 2010-03-03 00:00:00 |      6 |
|      1 | 2010-04-24 |    123 |  456 | 2010-04-24 01:52:20 |    100 |
+--------+------------+--------+------+---------------------+--------+
2 rows in set (0.00 sec)

Only 1 row is returned per "group". What does it mean when there is no "Count(*)", etc when using "Group By", and what are it uses? thanks.

Answer 1

By default, MySQL will return the value of the first row it encounters when executing the query. It's like it uses a default aggregate of arbitrary.

This is useful if you have a long column list where you know most of them are repeating, for example:

Login    LongName             City        PhoneNr      Time
Dude     Mr. Dude the 2nd     Jerk Town   12345678     13:01
Dude     Mr. Dude the 2nd     Jerk Town   12345678     13:05
Dude     Mr. Dude the 2nd     Jerk Town   12345678     13:12

Here you could group by login:

select LongName, City, PhoneNr, max(Time) from Logins group by login

Because you know that Long Name depends on Login, this will work as expected. All other DBMS systems I know of require you to explicitly specify group by login, LongName, City, PhoneNr. Even in MySQL that is considered good practice.

Answer 2

group by without an aggregate function in the select clause is the functional equivalent of using select distinct, although the database system might execute it differently.

BTW, the query select * from sentGifts group by whenSent is MySQL-specific: the select list normally includes only columns from the group by clause and aggregate functions (so PostgreSQL and probably MSSQL would not consider the query valid). Even though you tagged the question as "MySQL", your questions starts "In SQL" so I think it makes sense to point out that this behaviour is probably not part of an SQL standard.