Subqueries

[schug]

I am having trouble displaying the movie category that generated more rental transactions than any other movie categories. I must also display the number of rental transactions generated by this category.


select movie_category, COUNT(rental_id)
from rental JOIN movie USING(movie_id) join movie_type USING(movie_cat_id)
group by movie_category


MOVIE_CATE COUNT(RENTAL_ID)
---------- ----------------
Comedy 2
Horror 2
Western 1
Drama 5
SciFi 2


That is the output my current query gives me. I'm having trouble formulating a subquery that will help me just list the highest count(rental_id) along with it's specified category.

[amitpatel66]

I am having trouble displaying the movie category that generated more rental transactions than any other movie categories. I must also display the number of rental transactions generated by this category.


select movie_category, COUNT(rental_id)
from rental JOIN movie USING(movie_id) join movie_type USING(movie_cat_id)
group by movie_category


MOVIE_CATE COUNT(RENTAL_ID)
---------- ----------------
Comedy 2
Horror 2
Western 1
Drama 5
SciFi 2


That is the output my current query gives me. I'm having trouble formulating a subquery that will help me just list the highest count(rental_id) along with it's specified category.

Try this:

 
select movie_category, MAX(COUNT(rental_id)) OVER() from rental JOIN movie USING(movie_id) join movie_type USING(movie_cat_id)
group by movie_category
 
 

[Dave44]

Try this:

 
select movie_category, MAX(COUNT(rental_id)) OVER() from rental JOIN movie USING(movie_id) join movie_type USING(movie_cat_id)
group by movie_category
 
 

i think by using the max(count (rental_id)) ... your going to end up with each category having the count of whichever category has the largest count.

[Dave44]

I am having trouble displaying the movie category that generated more rental transactions than any other movie categories. I must also display the number of rental transactions generated by this category.


select movie_category, COUNT(rental_id)
from rental JOIN movie USING(movie_id) join movie_type USING(movie_cat_id)
group by movie_category


MOVIE_CATE COUNT(RENTAL_ID)
---------- ----------------
Comedy 2
Horror 2
Western 1
Drama 5
SciFi 2


That is the output my current query gives me. I'm having trouble formulating a subquery that will help me just list the highest count(rental_id) along with it's specified category.

you could use the row_number analytic to get an order by the largest count descending and then create an outer query that will select the row from the analytic function that is # 1

SELECT movie_cat,
       the_count
FROM   (SELECT   c.movie_cat,
                 COUNT(*) the_count,
                 ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) my_row
        FROM     rental a 
                 JOIN movie b ON a.movie_id = b.movie_id
                 JOIN movie_type c on a.movie_id = c.movie_id
        GROUP BY c.movie_cat)
WHERE  my_row = 1;
 

[schug]

Edited for being incorrect!!!!!!!!!

[schug]

Couldn't get that last suggestion to work.

 
select movie_category, count(rental_id)
from rental JOIN movie USING(movie_id) join movie_type USING(movie_cat_id)
where count(rental_id) = MAX(count(rental_id))
group by movie_category
 
 

Trying something like this but it will not work =(.

[amitpatel66]

Couldn't get that last suggestion to work.

 
select movie_category, count(rental_id)
from rental JOIN movie USING(movie_id) join movie_type USING(movie_cat_id)
where count(rental_id) = MAX(count(rental_id))
group by movie_category
 
 

Trying something like this but it will not work =(.

Try this:

 
SELECT * FROM
(select movie_category, count(rental_id) cnt
from rental JOIN movie USING(movie_id) join movie_type USING(movie_cat_id)
group by movie_category
ORDER BY cnt DESC)
WHERE ROWNUM < 2