The count is always 1, but what I need to get is the count or the number of news under each category. Like category Sport has 2 news and category Technology has 4 news:
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')
group by
n.NewsId, n.NewsTitle
Either group by NewsCategory.CategoryI or if you need all news-informations the OVER-clause:
select News.NewsId,
News.NewsTitle,
CategoryName,
CountInGroup = COUNT(*) OVER (PARTITION BY NewsCategory.CategoryId)
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')
Note that your GROUP BY n.NewsId, n.NewsTitle is redundant, it returns all records anyway. That's why you get count=1 every time.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments