CODEWITHSUNDEEP
mysqlsqlmariadb
Artur 'Dago' Wąsik
Artur 'Dago' Wąsik

Reputation: 98

SQL adding columns

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:

enter image description here

Got an idea of what's going on?

Upvotes: 2

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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

Upvotes: 2

Related Questions