Using count(*) and * in the same select clause gives an error. Why?

mysql> select COUNT(*), * from help_keyword;
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 help_keyword' at line 1

This one does not:

mysql> select COUNT(*), help_keyword.* from help_keyword;
+----------+-----------------+------+
| COUNT(*) | help_keyword_id | name |
+----------+-----------------+------+
|      452 |               0 | JOIN |
+----------+-----------------+------+
1 row in set 开发者_C百科(0.00 sec)

Why is that?

---

Because by using Count(*) you tell the database that you want an aggregation query, and therefore any columns or expressions in the select that do not include some aggregaton function (like count, min, max, etc.) are used to define the "buckets" that the aggregation will be performed on.. If there are any such expressions, you generally need to also include a Group By clause to explicitly list these columns/expressions. By including ALL the columns of the table, (which is what * does), you are saying aggregate over EVERY row in the table, which does not make sense. (In any proper relational tbale, there would always be only one row that matches every value for that row)

---

I'm not sure why it would produce a syntax error, but that's not very relevant. Perhaps they felt that using count(*) and * in a query was so clearly wrong that should be disallowed.

A query like that doesn't give a predictable result, so it shouldn't be used anyway. A random record will be picked to produce the values for the help_keyword.* part, and you can't control which record.

The use of the count aggregate means that the query is grouped, but as there is no group by clause it will result in a single group. Some other database systems, for example Microsoft SQL Server, doesn't allow you to include any fields at all in the result that are not specified in a group by clause. MySQL doesn't disallow this for some reason, perhaps with the motivation that it's up to you to determine if it will return anything that makes sense or not.

If you want to return values from fields, you would have to group on those fields, so that you know that the value is the same for all records in that group. Example:

select count(*), name
from help_keyword
group by name

This will produce a group for each name, and return you the name and how many records there are in each group.