SQL-How To Perform Query on Newly Created Column

Void S 0
Viewed 30

I have a full_name column, and then I create a new column based on it.

however i cannot perform aggregate or other kinds of functions on this new column.

how do we bypass this?

select full_name, 
case when full_name like '%b%' then 1 else 0 end as sample,
sum(sample)
from table
mysql sql
1 Answers

Based on your query you just need to use conditional aggregation as below:

select full_name, 
       sum(case when full_name like '%b%' then 1 else 0 end) as sample
from table
group by full_name;

MySQL Docs

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column:

More complicated way would be using an outer query as below which is pointless and excess :

   select full_name,
           sum(sample) as b_name_sum
   from ( select full_name, 
                  case when full_name like '%b%' then 1 else 0 end as sample
           from table
          ) as b_name                                              
 group by full_name;
Ergest Basha0
Related