Multiple count values on one column

Question

Hi All,

I want to query my database and get counts of the occurences of each id in a single column.

The two tables related to this query are: categories: this table has a list of all categories with titles

id | title
----------
1  | project
2  | tech
3  | other

category_news: this table is used to assign news items to categories, each news item can be in multiple categories

id | category_id | news_id
--------------------------
1  | 1           | 2
2  | 1           | 5
3  | 1           | 3
4  | 2           | 4
5  | 3           | 2
6  | 3           | 1

I would like to get a result of (doesnt have to be an array, I can sort out the results of a query, just using an array below for an example of the returned results):

array(
'project' => 3
'tech'    => 1
'other'   => 2
);

Where the key is the category title and the value is the total number of news items in each category. I know that I could do it with a query for each category:

select count(cn.category_id), c.title from category_news cn join categories c on c.id = cn.category_id where category_id = 1

But my question is, can I do it with one query to get all the counts? As if I have 100 categories thats 100 queries my way and I would love to do it in one.

Any help would be much appreciated

Regards

Luke

Answer 1

Try this:

SELECT     c.title, count(cn.category_id)
FROM       categories c
LEFT JOIN  categories_news cn
ON         c.id = cn.category_id
GROUP BY   c.title

Answer 2

Yes, you can. You have to use a query like the following (tested)

SELECT count( * ) , c.title FROM category_news cn JOIN categories c ON c.id = cn.category_id WHERE 1 GROUP BY cn.category_id

Answer 3

This will work(tested):

SELECT COUNT(cn.category_id), c.title 
    FROM category_news cn 
    JOIN categories c ON c.id = cn.category_id 
    GROUP BY cn.category_id