In SQL, why is "select *, count(*) from sentGifts group by whenSent;" ok, but when "*" and "count(*)
- by Jian Lin
In SQL, using the table:
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)
The following is OK:
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)
But suppose we want the count(*) to appear as the first column:
mysql> select count(*), * from sentGifts group by whenSent;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from sentGifts group by whenSent' at line 1
it gave an error. Why is it so and what is a way to fix it?
I realized that this is ok:
mysql> select count(*), whenSent from sentGifts group by whenSent;
+----------+------------+
| count(*) | whenSent |
+----------+------------+
| 1 | 2010-03-03 |
| 6 | 2010-04-24 |
+----------+------------+
2 rows in set (0.00 sec)
but what about the one above that gave an error? thanks.
