Here is the problem from leetcode. https://leetcode.com/problems/fix-product-name-format

Most people wrote this in mysql:

select lower(trim(product_name)) as product_name, date_format (sale_date, '%Y-%m') as sale_date, count(*) as total
from sales
group by 1, 2
order by 1, 2;

I still don't get why 'group by 1, 2' has to be used instead of 'group by product_name, sale_date'. In mysql, group by is executed after select, right? The alias should be considered from the trim() etc, right?

Can someone help me clarify this? Thanks!

leetcode problem screenshot

CodePudding user response：

Using the same thing as a select alias and a column name is likely to get you into trouble.

The sale_date returned by the select is just a month; the sale_date column presumably has multiple potential values per month. If you do:

select lower(trim(product_name)) as product_name, date_format (sale_date, '%Y-%m') as sale_date, count(*) as total
from sales
group by product_name, sale_date
order by 1, 2;

it is ambiguous as to whether you mean to group by sale_date the column or sale_date the alias. mysql will assume you mean the column, which will not aggregate your results by month.

Either explicitly repeat the select expression:

group by lower(trim(product_name)), date_format(sale_date, '%Y-%m')

or use the shorthand that allows you to reference select columns:

group by 1, 2

Or distinguish between what you select and what is stored:

select lower(trim(product_name)) as display_product_name, date_format (sale_date, '%Y-%m') as sale_month, count(*) as total
from sales
group by display_product_name, sale_month