views:

193

answers:

2

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.

+3  A: 

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.

Andomar
It's not exactly random, because MySQL returns the same row from the group repeatedly. It's more like arbitrary. In practice, it's the first row in the group, with respect to the physical storage of the rows. This may not be the row with the lowest value in any given column.
Bill Karwin
@Bill Karwin: Cool, arbitrary is a better name. Btw it's not always the first physical row: if you're ordering using an index, it will be the first row found in that index in the direction MySQL is scanning
Andomar
Aha! Good to know. In any case, it's pretty much up to the internal implementation, since it's undefined by the SQL standard.
Bill Karwin
A: 

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.

Tomislav Nakic-Alfirevic
For columns not grouped on, MySQL returns just one row. Distinct would return all rows with different values
Andomar
I was just updating my answer to explain better what I ment: `group by` generally behaves like `select distinct` when the `select` clause explicitly lists the columns listed in the `group by` clause.
Tomislav Nakic-Alfirevic