CODEWITHSUNDEEP
sqloracle-database
user2793442
user2793442

Reputation: 129

Oracle SQL, using the count function

I am having great difficulty with a particular select statement involving count. What I am supposed to do is:

For each movie category, now project the movie category name, and the number of videos of movies in that category.

select category_name, count(movie_num)
from movie, movie_category
where movie.category_code = movie_category.category_code
group by category_name;

While this statement produces some results, it does not project the correct number of results. What I get from the above statement:

CATEGORY_N COUNT(MOVIE_NUM)
---------- ----------------
Action                    7
Classic                   4
Family                    4
Drama                     4
Comedy                    1

Although you can't see my tables, hopefully these will give you an idea of what they are:

Movie(MOVIE_NUM, movie_title, movie_director_lname, movie_yr_released, movie_rating,
category_code)
foreign key(category_code) references movie_category

Movie_category(CATEGORY_CODE, category_name)

In the end, this is what I should be getting:

CATEGORY_N   COUNT(*)                                                           
---------- ----------                                                           
Action             20                                                           
Family             10                                                           
Classic             6                                                           
Drama               5                                                           
Comedy              1

But I cannot figure it out. Hopefully someone can point me in the right direction!

Upvotes: 0

Views: 143

Answers (1)

Nirmo
Nirmo

Reputation: 26

MOVIE_NUM may contains null values.

Use

SELECT category_name, COUNT (*)
  FROM movie, movie_category
 WHERE movie.category_code = movie_category.category_code
GROUP BY category_name;

or 

SELECT category_name, COUNT (NVL (movie_num, 1))
  FROM movie, movie_category
 WHERE movie.category_code = movie_category.category_code
GROUP BY category_name;

Upvotes: 1

Related Questions