SQL group by alias


A friend at work asked me today about grouping by only part of a column when using MySQL. He wanted to group by a string column, but only part of the string contained what he wanted.

So basically I gave him a query that looks similar to the one below:

select count(*), substring(string_column, 1, 10) 'string_alias' from table t group by 'string_alias';

Basically, the substring function extracts the useful information, the ‘string_column’ is the name of the column being grouped on. The indexes 1 and 10 represent the start of the substring, and 10 for the number of characters being selected.

So basically, the query groups on the first 10 characters in the string column. The alias is the main key ingredient, allowing the group by statement access to the substring we’ve selected.

Anyways, I hope that helps someone else.


This entry was posted in mysql.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>