I had a bit of fun with MySQL earlier this week when trying to explain a non obvious “group by” behavior. It’s fairly common to want to manipulate a field in order to transform it into something more useful. The difficulty arises when you want to keep the original name. Below is some SQL code that highlights the odd behavior.
With the second to last query it’s not obvious which id field the group by is referring to: the original from the table or the derived field? It turns out it’s the original field which can cause problems if you’re unaware of this subtlety. There are a few different ways to deal with this situation, including grouping by the derivation formula, but my favorite is to use a brand new field as in the last example above.