SQL Server Select Count in Join

Question

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')

Are you wanting to aggregate the results by newsid? Meaning do you want multiple records returned? If so, you are simply missing the group by clause. Add this to the end: group by news.newsid, news.newstitle... If not, you should better define your question... — sgeddes
– sgeddes, Commented Jul 27, 2016 at 2:30

Vijunav Vastivch · Accepted Answer · 2016-07-29 00:30:28Z

0

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

edited Jul 29, 2016 at 0:30

answered Jul 27, 2016 at 2:30

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lucy Over a year ago

It's working, thank you :) But the count is always 1, I need to get the Count or the number of news under each category. like category Sport has 2 news and category Technology has 4 news, Can you please help me to solve this, Thank you

Vijunav Vastivch Over a year ago

you mean count per each category?

Lucy Over a year ago

Yes ... Thank you so much for your help :)

Vijunav Vastivch Over a year ago

helloo are you there?

Vijunav Vastivch Over a year ago

i don't even know your db design.. just making some solution for you. Hope you can add more details here for your result

M.Ali · Accepted Answer · 2016-07-27 02:29:38Z

You need to group by the column NewsId and NewsTitle

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 IN ('travel','Technology','Sport'))
GROUP BY News.NewsId
        , News.NewsTitle

Or you can write the query as

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

Gordon Linoff · Accepted Answer · 2016-07-27 02:34:30Z

0

Just write this using two joins and a group by:

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

answered Jul 27, 2016 at 2:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Lucy Over a year ago

It's working, thank you :) But the count is always 1, I need to get the Count or the number of news under each categories. like category Sport has 2 news and category Technology has 4 news, Can you please help me to solve this, Thank you

Collectives™ on Stack Overflow

SQL Server Select Count in Join

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related