nael_d nael_d - 6 months ago 8
MySQL Question

How to get the count number of news as an alias column in MySQL query?

I have two tables (news, seen) and the relation between them is 1 to many: one news has many sees.

I need an SQL Query to get each news with an additional alias column valued by the sum of each news sees.

Here is the structure of the both tables:




News:
id_news, news_title, news_text, date, time, pic

Seen: id_seen, id_user, id_news, ip, fulldate, time




I need a query that returns the result like:

id_news | news_title | news_text | fulldate | fulltime | pic | seen_count (alias)
--------------------------------------------------------------------------------------
1 | Title Blah | Text Blah | Jun/03/2016 | 19:54:25 | 9.jpg | 934
2 | Title Blah | Text Blah | Jun/01/2016 | 19:24:25 | 6.jpg | 549





I tried lot of times but really couldn't achieve that.

Answer
select news.*, count(seen.id_seen) as seen_count from news 
left join seen on seen.id_news=news.id_news
group by news.id_news