How to assign a new column name to the sum of columns aggregated in a mysql query

Question

I need to assign a name to the sum of columns aggregated in a mysql query.

This is what is tried:

SELECT 
     JSON_LENGTH(comments) as c_total, 
     JSON_LENGTH(likes) as l_total, 
     (c_total+l_total) as total 
FROM posts

Comments and likes columns holds data in json data type. JSON_LENGTH returns the length of the array. How can ? get the total value in above example ?

question from:https://stackoverflow.com/questions/65648971/how-to-assign-a-new-column-name-to-the-sum-of-columns-aggregated-in-a-mysql-quer

Answer 1

You can reference select fields if they're in subqueries:

SELECT c_total + l_total AS Total
FROM (
    SELECT
         JSON_LENGTH(comments) as c_total,
         JSON_LENGTH(likes) as l_total,
         (c_total+l_total) as total
    FROM posts
) A
;

otherwise I think you have to repeat the logic:

SELECT 
     JSON_LENGTH(comments) as c_total, 
     JSON_LENGTH(likes) as l_total, 
     (JSON_LENGTH(comments) + JSON_LENGTH(likes)) as total 
FROM posts
;