Oracle SQL, using the count function

Question

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!

what happens when you change count(movie_num) to count(*)? — zundarz
– zundarz, Commented Nov 1, 2013 at 3:35
Are you sure that the column movie_num is not null and primary key? Can you post the DDL script of the two tables? — Marco Baldelli
– Marco Baldelli, Commented Nov 1, 2013 at 7:32

Nirmo · Accepted Answer · 2013-11-01 12:12:33Z

1

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;

edited Nov 1, 2013 at 12:12

user330315

answered Nov 1, 2013 at 11:14

Nirmo

261 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL, using the count function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related