CODEWITHSUNDEEP
mysqlsql
FrozenHeart
FrozenHeart

Reputation: 20764

How to create an alias for the part of SQL query without SELECTing it

Is there any way to create an alias for the part of SQL query without SELECTing it?

For example, if I have the following query:

SELECT 
    product_name,
    SUM(SUBSTRING_INDEX(company_name, '|', -1) LIKE '%live%')  AS count_live,
    SUM(SUBSTRING_INDEX(company_name, '|', -1) LIKE '%demo%')  AS count_demo,
    SUM(SUBSTRING_INDEX(company_name, '|', -1) NOT LIKE '%live%' AND SUBSTRING_INDEX(company_name, '|', -1) NOT LIKE '%demo%') AS count_other,
    COUNT(*) AS total
FROM `foo` GROUP BY product_name
;

can I create an alias for the SUBSTRING_INDEX(company_name, '|', -1) to reduce the code duplication?

Upvotes: 1

Views: 73

Answers (1)

Arth
Arth

Reputation: 13110

Unfortunately you can't alias part of a SELECT, you could write a function that does exactly SUBSTRING_INDEX(field, '|', -1) but this would only get you as far as MY_FUNCTION(field).

You could also do the substring bit first in a subquery and then wrap it in a further select:

  SELECT product_name,
         SUM(status LIKE '%live%') AS count_live,
         SUM(status LIKE '%demo%') AS count_demo,
         SUM(status NOT LIKE '%live%' AND status NOT LIKE '%demo%') AS count_other,
         COUNT(*) AS total
    FROM ( 
      SELECT product_name,
             SUBSTRING_INDEX(company_name, '|', -1) status,
        FROM foo
         ) s
GROUP BY product_name

I'd be worried this might use a lot of memory building the subquery results first and hamper performance. It's also still quite complicated.

Or you could split out the 'live/demo' from the company_name field in your table structure as mentioned on your previous question. ;)

Upvotes: 3

Related Questions