CODEWITHSUNDEEP
mysqlalias
Glenn
Glenn

Reputation: 59

MySQL alias on group by

I have the following mysql query:

insert into my_table (col_a, col_b)
select
  col_a, 
  col_b,
  if(foo, bar, baz) as my_alias
from qux
group by my_alias
having my_alias is not null

How could I rewrite the query without the select-ing the generated field my_alias?

For example, I'm trying to achieve something like this where I only insert on col_a, col_b:

insert into my_table (col_a, col_b)
select
  col_a, 
  col_b
from qux
group by if(foo, bar, baz) as my_alias
having my_alias is not null

I found some previous answers where this is done by making a sub-select. Can this be achieved without one? Thanks in advance.

Upvotes: 1

Views: 371

Answers (1)

Arnold Daniels
Arnold Daniels

Reputation: 16563

The manual tells us:

MySQL permits expressions in GROUP BY clauses, so the alias is unnecessary:

SELECT id, FLOOR(value/100)   FROM tbl_name   GROUP BY id,
FLOOR(value/100);

The HAVING filters out the NULL record at the end. We can also use a WHERE to filter out the NULL records at forehand. That would mean that this should work:

insert into my_table (col_a, col_b)
select
  col_a, 
  col_b
from qux
where if(foo, bar, baz) is not null
group by if(foo, bar, baz)

Upvotes: 1

Related Questions