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

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

Up vote 1 down vote favorite share g+ share fb share tw.

In SQL, using the table: mysql> select * from sentGifts; +--------+------------+--------+------+---------------------+--------+ | sentID | whenSent | fromID | toID | trytryWhen | giftID | +--------+------------+--------+------+---------------------+--------+ | 1 | 2010-04-24 01:52:207 | 123 | 456 | 2010-04-24 01:52:20 | 100 | | 2 | 2010-04-24 01:52:207 | 123 | 4568 | 2010-04-24 01:56:04 | 100 | | 3 | 2010-04-24 01:52:207 | 123 | NULL | NULL | 1 | | 4 | 2010-04-24 01:52:207 | NULL | 111 | 2010-04-24 03:10:42 | 2 | | 5 | 2010-04-24 01:52:207 | 11 | 22 | 2010-04-247 00:00:00 | 6 | | 6 | 2010-04-24 01:52:207 | 11 | 222 | 2010-04-24 03:54:49 | 6 | | 7 | 2010-04-24 01:52:207 | 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-04-24 01:52:207 | 11 | 22 | 2010-04-247 00:00:00 | 6 | 1 | | 1 | 2010-04-24 01:52:207 | 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-04-24 01:52:207 | | 6 | 2010-04-24 01:52:207 | +----------+------------+ 2 rows in set (0.00 sec) but what about the one above that gave an error?

Thanks. Sql mysql select count group-by link|improve this question edited 10-04-24 at 18:54 asked 10-04-24 at 18:21?15.7k750153 68% accept rate.

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.

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.

1 Yes I think this is the most direct answer to his question. – Mark Byers Apr 24 '10 at 19:01.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.