SQL Server How to get the number of news under each category

Question

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

Is that really valid SQL Server syntax? Add some sample table data, and the expected result. — jarlh, Jul 27 '16 at 11:13
the result of your query will not be 1 but something like 'syntax error near from' — GuidoG, Jul 27 '16 at 11:17

ADyson · Answer 1 · 2016-07-27T12:02:56.393

2

This should do it I think:

select
  C.CategoryName,
  count(N.NewsId) as Total 
from News N 
  inner join NewsCategory NC 
    on N.NewsId = NC.NewsId
  inner join Category C
    on NC.CategoryId = C.CategoryId
where 
  upper(C.CategoryName) in ('TRAVEL', 'TECHNOLOGY', 'SPORT')
group by 
  C.CategoryName

edited Jul 27 '16 at 12:02

answered Jul 27 '16 at 11:19

ADyson

57,178
14
51
63

But it seems that OP wants `News.NewsId, News.NewsTitle` too – Tim Schmelter Jul 27 '16 at 12:19
@TimSchmelter, Exactly ... Can you please explain what is COUNT(*) OVER (PARTITION BY NewsCategory.CategoryId) ...I am new to Database and never used OVER (PARTITION BY) before – Lucy Jul 27 '16 at 12:27
@Lucy: look at the documentation of the [OVER-](https://msdn.microsoft.com/en-us/library/ms189461.aspx)clause. [This stackoverflow question](http://stackoverflow.com/questions/6218902/the-sql-over-clause-when-and-why-is-it-useful) might also help to understand it. You can use it with any aggregate (or ranking)-function. So not only with `COUNT`. The `PARTITION BY` specifies the "groups" you want to aggregate(count in this case). – Tim Schmelter Jul 27 '16 at 12:46
@TimSchmelter: Thank you :) – Lucy Jul 27 '16 at 12:49
@TimSchmelter fair enough, the question text didn't state that explicitly and I just assumed the inclusion was a mistake based on the OP misunderstanding the SQL query. The above will still work as a basic count, but yours works if you want to list all the actual items as well, so it's probably preferable. – ADyson Jul 27 '16 at 12:51
@TimSchmelter. Hi :) Can you please help me to solve this problem please? thank you so much http://stackoverflow.com/questions/39066940/linq-how-group-the-data-to-make-list?noredirect=1#comment65484575_39066940 – Lucy Aug 21 '16 at 20:54
Sorry Lucy, was on vacation ;) – Tim Schmelter Aug 22 '16 at 08:35

score 1 · Accepted Answer · answered Jul 27 '16 at 11:15

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.

Thank you so much :) , Your query is working. But I tried to to run this one but it not working 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') group by NewsCategory.CategoryId — Lucy, Jul 27 '16 at 12:15

score 0 · Answer 3 · answered Jul 27 '16 at 11:17

To get the Category and number of

SELECT NewsCategory.CategoryId, Category.CategoryName, COUNT(News.NewsId) as Total 
FROM News  
INNER JOIN NewsCategory on News.NewsId = NewsCategory.NewsId 
INNER JOIN Category on Category.CategoryId = NewsCategory.CategoryId
                    AND UPPER(Category.CategoryName) IN ('TRAVEL','TECHNOLOGY','SPORT')
GROUP BY 
NewsCategory.CategoryId, Category.CategoryName

SQL Server How to get the number of news under each category

3 Answers3