Normal columns are accompanied by agregate functions, but not in
I knew that regular columns followed by aggregate functions are only allowed if they are followed by Group By
.
But why then do the following work?
mysql> select payee,sum(amount) from checks;
+---------+-------------+
| payee | sum(amount) |
+---------+-------------+
| Ma Bell | 893.76 |
+---------+-------------+
1 row in set (0.00 sec)
source to share
This behavior is an "extension" for MySql :
MySQL extends the usage
GROUP BY
so that the select list can refer to non-aggregated columns not listed in the clauseGROUP BY
.
However, This behavior is actually configurable in MySql :
ONLY_FULL_GROUP_BY
Do not allow queries for which the selected list or (as from MySQL 5.1.10) The HAVING list refers to non-aggregated columns that are not specified in the GROUP BY clause.
It is best to respect GROUP BY
and add all non-aggregated columns, especially if there is a chance that you might someday be able to upgrade to an enabled server ONLY_FULL_GROUP_BY
.
source to share
EDIT Manual link that explains better than me and note the details: http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html
This should work fine. Without an aggregate function, it does this on all rows returned in the query. The reason you see little is I think this is rarely what you are actually trying to achieve. You will often drop a group when you have a where clause that you know will only return the things you plan to group from. those. if you ask:
select payee,sum(amount) from checks where payee = 'Ma Bell'
The group is technically redundant in the following:
select payee,sum(amount) from checks where payee = 'Ma Bell' group by payee
Personally - I usually include the GROUP BY clause as I THINK it is more consistent cross-platform ... not 100% sure.
Again, in your previous question, I would ask again - even if it technically works, you get the result you want without the where clause?
source to share