In SQL, what does Group By mean without Count(*), or Sum(), Max(), avg(), ..., and what are some use
- by Jian Lin
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.
