Lucy Lucy - 4 months ago 6
SQL Question

SQL Server Select Count in Join

I need to get the count of News , it gives me error

Column 'News.NewsId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

select News.NewsId
, News.NewsTitle
, COUNT(News.NewsId) as Total
from News inner join NewsCategory on News.NewsId = NewsCategory.NewsId
where NewsCategory.CategoryId in (Select CategoryId
from Category
where CategoryName = 'travel'
or CategoryName = 'Technology'
or CategoryName = 'Sport')

Answer

use ti like this:

select    News.NewsId, News.NewsTitle, COUNT(News.NewsId) as Total from News  group by  News.NewsId, News.NewsTitle
inner join 
NewsCategory
on News.NewsId = NewsCategory.NewsId where NewsCategory.CategoryId in (Select CategoryId from Category where CategoryName = 'travel' or  CategoryName = 'Technology' or CategoryName = 'Sport')'Technology' or CategoryName = 'Sport')

You are using count that's why you need to group by the field,include at your count(News.NewsId)

This one displays count for every category according to each NewsId:

select count(1), a.NewsId, a.NewsTitle,b.category from
(select  NewsId, NewsTitle from tbl_news)as a


left JOIN

(select    NewsId, categoryid,Category  from tbl_category) as b
on a.newsid = b.newsid GROUP BY a.NewsTitle,b.category