In SQL, why is "select *, count(*) from sentGifts group by whenSent;" ok, but when "*" and "count(*)" is switched position, then it gives an error?

Question

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 gives 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.

Answer 1

I'm not sure why it gives an error, but if you write:

 SELECT COUNT(*), sentGifts.* FROM sentGifts GROUP BY whenSent

it doesn't give that error.

Also it fails with the same error even without the group by:

 SELECT whenSent, * FROM sentGifts 

According to the documentation:

Use of an unqualified * with other items in the select list may produce a parse error. To avoid this problem, use a qualified tbl_name.* reference

So my guess is that an unqualified * if it appears must be immediately after the SELECT keyword. In general you shouldn't do it. It may stop working in later versions of MySQL.

I also suggest you read the documentation regarding selecting nonaggregated columns when using a GROUP BY:

MySQL extends the use of GROUP BY so that you can use nonaggregated columns or calculations in the SELECT list that do not appear in the GROUP BY clause

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

So your results are indeterminate. Don't do this.

Also selecting * is a bad practice. You should explicitly list the columns you need even if it is all of them.

Answer 2

From the MySQL reference manual:

Use of an unqualified * with other items in the select list may produce a parse error. To avoid this problem, use a qualified tbl_name.* reference.

So instead of:

SELECT count(*), * FROM sentGifts GROUP BY whenSent

Use

SELECT count(*), sentGifts.* FROM sentGifts GROUP BY whenSent

Or

SELECT count(*), sg.* FROM sentGifts as sg GROUP BY whenSent