MySQL join / sum value from one table colum with a count values from another

debugcn Published at Dev

user3403520

Table 1 - "news"

--------------------------------------
|news_id|news_title|fb_comments_count|
--------------------------------------
|     1|        XYZ|                0|
|     2|        ABC|               32|
|     3|        DEF|                5|
--------------------------------------

Table 2 - "news_comments"

------------------------------
|com_id|   news_id|   comment|
------------------------------
|     1|         1| blablabla|
|     2|         2| test     |
|     3|         1| comment  |
|     4|         1| asdf     |
------------------------------

I need to get total comments sum for every single news, where facebook comments count from the first table and total count of comments for every specific news from the other table, are counted together.

Like this - "Total comments":

-----------------------------------
|   id | news_id  | total comments (fb_comments + count of comments from comments table)
-----------------------------------
|     1|        2 |             33|
|     2|        3 |              5|
|     3|        1 |              3|
-----------------------------------

Thanks in advance! :)

wvdz

A simple join and a group by can count all your comments with COUNT(). Then simply add this to the fb_comment_count. We use a LEFT JOIN to make sure it also includes news_id's that don't occur in news_comments, and apply the COUNT() only to c.com_id, to ensure this will give 0 for these situations when there are no news_comments for this news_id.

SELECT n.news_id, COUNT(c.com_id) + n.fb_comments_count AS total_comments
FROM news n
LEFT OUTER JOIN news_comments c ON c.news_id = n.news_id
GROUP BY n.news_id, n.fb_comments_count

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-06-26

Comments

0 comments

From Dev

Related Related

Article

MySQL join / sum value from one table colum with a count values from another

MySQL join / sum value from one table colum with a count values from another

Join a columns count from another table

MySQL query with COUNT and join column from another table

mySQL SELECT from one table, JOIN from another, then SELECT from the new one

Selecting SUM+COUNT from one table and COUNT from another in Single query

join count from one table to select from another - mysql

Select values from one table depending on referenced value in another table

Return Sum from another table in join with duplicates

MySQL count of count, use result from one table with another

MySQL: Is there a way to insert values from one table to another?

Joining row from one table with the sum value from another table

How can I get value from one table and array of values from another join table in one mysql query?

MYSQL join one colum from one table to two count() in another

MySQL query to sum one column and count another column, from two tables, based on a common value?

MySQL set column value from another table (JOIN 3 tables)

MySQL - Join & Count rows from another table

mysql insert unique values from one column to a column of another table

Retriving the count from one table given a value from another

MySQL Count values from one column with selecting a value from another column

Mysql and php sum of values from colum depending on date

Join multiple values from one column, selected from another table

MySQL: Is there a way to insert values from one table to another?

Select from one table and count from another

Display 2 columns from one table having max count in column 3 and display computed sum of values from another table

Select * as well as count/sum from another table

PHP PDO count and sum value from MySQL table

MySQL query - select from one, count from another table

how to show 2 value and one values count from 3 table using mysql

Subtract total sum of a colum, From the total sum of a colum from another table

Join in MYSQL (Count from One table and list from one table)