How do I count 2 columns and then aggregate?

I have 2 simple tables:


categories
-----------
category_id
category_name


movies
--------
id
movie_name
category_id
subcategory_id


As you can see, the movies can be given a category and a sub-category.


I can select the categories and count how many movies there are in each category like this:


SELECT category_name, COUNT(id) AS Number_of_Movies
FROM `categories`
JOIN `movies` USING(category_id)
GROUP BY category_id
ORDER BY category_name ASC


But how do I include the sub-categories in the count?

 

Okay, I've just done the following and it seems to work:

SELECT category_name, (SELECT COUNT(id)) + (SELECT COUNT(subcategory_id)) as Number_of_Movies
FROM `categories`
JOIN `movies` USING(category_id)
GROUP BY category_id
ORDER BY category_name ASC

Is that the best way to do this kind of query?

 

Actually, I was wrong; that doesn't work.

Any ideas?

 

Okay, I seem to have worked it out:

SELECT category_name, COUNT(id) as Num
FROM `categories`
JOIN `movies`
WHERE movies.category_id = categories.category_id
OR movies.subcategory_id = categories.category_id
GROUP BY categories.category_id
ORDER BY categories.category_id ASC

But is that the best way of doing these kinds of queries? Can you see any problems?

 

Another question . . .

id and categories.category_id are primary keys.

Should I make subcategory_id an index?

 

do you need these column : [category/subcategory name] [number of movies] ?
if yes, you should make query for category and then union with sub category.

 

Looks like the most efficient way to accomplish this to me.

Talking semantics here, but I prefer to keep the JOIN conditions out of the where clause. In this case it doesn't matter but on complex queries it makes it more difficult to understand what's going on.

SELECT
    category_name,
    COUNT(id) AS Num
FROM
    categories
JOIN `movies` ON (
    movies.category_id = categories.category_id
    OR movies.subcategory_id = categories.category_id
)
GROUP BY
    categories.category_id
ORDER BY
    categories.category_id ASC

Code (markup):

 

This will work

   
SELECT
    B.CATEGORY_NAME AS [Category],
    COUNT(A.ID) AS [NoOfMovies]
FROM MOVIES A
JOIN CATEGORIES B
    ON A.CATEGORY_ID = B.A.CATEGORY_ID OR A.SUBCATEGORY_ID = B.A.CATEGORY_ID
Group By
    B.CATEGORY_NAME

Code (markup):

 

Yes.

 

Thank you for the help, guys.