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

Lucy

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
Tim Schmelter

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Java

SQL Server Add row number each group

From Dev

How to get current identity number of specific table in sql server compact

From Dev

How can I get category name of post in news module from category module in CodeIgniter HMVC

From Dev

Sql getting count of each category

From Dev

How get values categories an count products for each category in one query?

From Dev

How to get RANDOM records from each category in MySQL?

From Dev

How to select all categories and count number of each category articles in MySQL

From Dev

How do I select an exact number of articles for each category?

From Dev

how to sort post titles under their correct category names PHP and SQL

From Dev

How to get number of rows in a grouped-by category in pandas

From Dev

SQL how to find the average users from each category on a typical date

From Dev

How to iterate pages and get the link and title of each news article

From Dev

How to get current identity number of specific table in sql server compact

From Dev

Sql getting count of each category

From Dev

How to get divs to go under each other

From Dev

How can I create a directory for each category under _posts in Jekyll?

From Dev

How to get elements from table by row number in sql server

From Dev

How to get continuous number based on item number for each category in excel?

From Dev

How to select all categories and count number of each category articles in MySQL

From Dev

how to sort post titles under their correct category names PHP and SQL

From Dev

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

From Dev

How can I get Access SQL to return a dataset of the largest value in each category?

From Dev

Sql Server - Fastest way to get number of times value changed for each ID

From Dev

SQL Server query to get the total for each row

From Dev

How to get category count using group by in sql server?

From Dev

Get total number of existing subcategories of each WooCommerce parent category

From Dev

SQL Server : get occurrence of each string in a table

From Dev

How to get the number of orders for each category?

From Dev

How to get the maximum row_number() for each group in hive sql

Related Related

  1. 1

    SQL Server Add row number each group

  2. 2

    How to get current identity number of specific table in sql server compact

  3. 3

    How can I get category name of post in news module from category module in CodeIgniter HMVC

  4. 4

    Sql getting count of each category

  5. 5

    How get values categories an count products for each category in one query?

  6. 6

    How to get RANDOM records from each category in MySQL?

  7. 7

    How to select all categories and count number of each category articles in MySQL

  8. 8

    How do I select an exact number of articles for each category?

  9. 9

    how to sort post titles under their correct category names PHP and SQL

  10. 10

    How to get number of rows in a grouped-by category in pandas

  11. 11

    SQL how to find the average users from each category on a typical date

  12. 12

    How to iterate pages and get the link and title of each news article

  13. 13

    How to get current identity number of specific table in sql server compact

  14. 14

    Sql getting count of each category

  15. 15

    How to get divs to go under each other

  16. 16

    How can I create a directory for each category under _posts in Jekyll?

  17. 17

    How to get elements from table by row number in sql server

  18. 18

    How to get continuous number based on item number for each category in excel?

  19. 19

    How to select all categories and count number of each category articles in MySQL

  20. 20

    how to sort post titles under their correct category names PHP and SQL

  21. 21

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

  22. 22

    How can I get Access SQL to return a dataset of the largest value in each category?

  23. 23

    Sql Server - Fastest way to get number of times value changed for each ID

  24. 24

    SQL Server query to get the total for each row

  25. 25

    How to get category count using group by in sql server?

  26. 26

    Get total number of existing subcategories of each WooCommerce parent category

  27. 27

    SQL Server : get occurrence of each string in a table

  28. 28

    How to get the number of orders for each category?

  29. 29

    How to get the maximum row_number() for each group in hive sql

HotTag

Archive