CODEWITHSUNDEEP
mysql
Elona
Elona

Reputation: 137

Count posts for each category

I have 3 MySQL tables:

Table1: Posts

id | date | postname | content
1 | 2014-07-17 | Post 1 | Content of post 1
2 | 2014-07-18 | Post 2 | Content of post 2
3 | 2014-07-19 | Post 3 | Content of post 3

Table2: Categories

id | category
1 | Category 1
2 | Category 2
3 | Category 3

Table3: Connection

id | post_id | category_id
1 | 1 | 1
2 | 1 | 2
3 | 2 | 2
4 | 2 | 3
5 | 3 | 1
6 | 3 | 2
7 | 4 | 3

I'm trying to display category names with number of posts to each category: example in this way:

Category 1 - 2 posts
Category 2 - 2 Posts
category 3 - 3 posts

I'm trying something like:

Select * FROM posts
inner join 
connection on...

Upvotes: 2

Views: 1114

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

Since you just need the post count per category, you don't need to join the posts table. Joining to the connection table and grouping by the category will suffice.

select c.category, count(*)
from categories c
join connection co on co.category_id = c.id
group by c.category

Upvotes: 5

Related Questions