SQL adding columns

Question

I have a problem with the basic operation of summing two columns. It's simple but it does not work.

I get results 5 + 5 = 8, 3 + 7 = 7

This is the query:

select 
    `wp_posts`.ID ,
    (select count(*) from `co_likes` 
     where `wp_posts`.`ID` = `co_likes`.`id_post` 
       and `co_likes`.`deleted_at` is null) as `like_count`, 
    (select count(*) from `wp_comments` 
     where `wp_posts`.`ID` = `wp_comments`.`comment_post_ID`) as `comment_count` ,
    (`comment_count`+`like_count`) as 'total_sum'
from 
    `wp_posts` 
where 
    `post_type` in ('post', 'co_post') 
    and `post_status` = 'publish' 
order by 
    (comment_count+like_count) desc;

And this is the result:

Got an idea of what's going on?

you can't use column-aliases previously defined in the same query..i am not sure how this query ran in the first place. — Vamsi Prabhala
– Vamsi Prabhala, Commented Oct 3, 2017 at 13:49

Gordon Linoff · Accepted Answer · 2017-10-03 13:51:26Z

You cannot use column aliases in the same select (or where) where they are defined. In your case, the best best is probably a subquery:

select p.*, (`comment_count`+`like_count`) as total_sum
from (select `wp_posts`.ID ,
             (select count(*) from `co_likes` where `wp_posts`.`ID` = `co_likes`.`id_post` and `co_likes`.`deleted_at` is null) as `like_count`, 
             (select count(*) from `wp_comments` where `wp_posts`.`ID` = `wp_comments`.`comment_post_ID`) as `comment_count` ,
      from `wp_posts` 
      where `post_type` in ('post', 'co_post') and `post_status` = 'publish' 
     ) p
order by total_sum desc;

If you only want to order by the sum, but don't need to see it, you can put the sum in the order by:

      select `wp_posts`.ID ,
             (select count(*) from `co_likes` where `wp_posts`.`ID` = `co_likes`.`id_post` and `co_likes`.`deleted_at` is null) as `like_count`, 
             (select count(*) from `wp_comments` where `wp_posts`.`ID` = `wp_comments`.`comment_post_ID`) as `comment_count` ,
      from `wp_posts` 
      where `post_type` in ('post', 'co_post') and `post_status` = 'publish' 
      order by (like_count + comment_count) desc

Collectives™ on Stack Overflow

SQL adding columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related