By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,310 Members | 818 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,310 IT Pros & Developers. It's quick & easy.

select count(table) problem

P: 3
Hello.
I have a database with movies similar with imdb and i want to find out which directors have directed both thriller and drama movies.
The output i want is like this "DIR_NAME,amount of thriller movies,amout of drama movies"
First i did a check to see the expected number.Checkin only directors that have made thriller movies with
Expand|Select|Wrap|Line Numbers
  1. select DIR_NAME, count(GENRE) 
  2. from MGENRES, DIRECTORS, DIRECTED_BY
  3. where GENRE = 'Thriller' and MGENRES.MOVIE_ID = DIRECTED_BY.MOVIE_ID and DIRECTED_BY.DIR_ID = DIRECTORS.DIR_ID
  4. group by DIR_NAME
  5. order by count(GENRE) desc
  6.  
I found 3600 results and max count per director 36,Doing the same with keyword drama i found 19000 results and max count per director 200.So results must be 3600 or less and max count 200.
I use this query for finding out directors taht have made thriller and drama movies
Expand|Select|Wrap|Line Numbers
  1. select DIR_NAME, count(t1.GENRE) as Thriller, count(t2.GENRE) as Drama
  2. from MGENRES as t1, MGENRES as t2, DIRECTORS, DIRECTED_BY as t3, DIRECTED_BY as t4
  3. where t1.GENRE = 'Thriller' and t1.MOVIE_ID = t3.MOVIE_ID and t3.DIR_ID = DIRECTORS.DIR_ID and t2.GENRE = 'Drama' and t2.MOVIE_ID = t4.MOVIE_ID and t4.DIR_ID = t3.DIR_ID
  4. group by DIR_NAME
  5. order by count(t1.GENRE) desc 
  6.  
The directors results seems good but the count is for sure wrong.I get the same count for each row count(t1.GENRE) = count(t2.GENRE) and very big amounts that can't be true 1000 thriller movies directed by one director etc which is fault.
Any clue?
Dec 6 '06 #1
Share this Question
Share on Google+
3 Replies

gateshosting
P: 25
I don't think that will work because you are still doing a record count on the rows, which because you have joined the 2 tables for thriller and Drama. They row will give you a count of 1 no matter what genra it is.

Try something like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   DIR_NAME,
  3.   SUM(CASE WHEN [T1].[GENRE] = 'Thriller' THEN 1 ELSE 0) AS THRILLERS,
  4.   SUM(CASE WHEN [T1].[GENRE] = 'Drama' THEN 1 ELSE 0) AS DRAMAS
  5. FROM
  6.   MGENRES [M] LEFT OUTER JOIN
  7.   DIRECTEDBY [DB] ON [M].[MOVIE_ID] = [DB].[MOVIE_ID] LEFT OUTER JOIN
  8.   DIRECTORS [D] ON [DB].[DIR_ID] = [D].[DIR_ID]
  9. GROUP BY DIR_NAME
  10.  

This would give you the count for each of these genres. However, I would suggest creating a view of the count of each genre/director... then join that view.

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   COUNT(*),
  3.   GENRE,
  4.   DIR_ID,
  5.   DIRECTOR_NAME
  6. FROM
  7.   MGENRES M LEFT OUTER JOIN
  8.   DIRECTEDBY D ON M.DIR_ID = D.DIR_ID LEFT OUTER JOIN
  9.   DIRECTORS DRS ON D.DIR_ID = DRS.DIR_ID
  10. GROUP BY
  11.   DIR_ID, DIRECTOR_NAME, GENRE
  12.  
I only showed you the first way so you can have some creative ideas for future scripts. The second is the best way to do it. You can even build it into a stored procedure to pass dir_id, or genre... It would be quicker.

Best regards,

Michael C. Gates
Dec 6 '06 #2

P: 3
Thx for reply.I understand ur 2nd query but your 1st didn't get it.Tried it and gave me errors.The 2nd one is working ok.I thought it like u said with views and so i created two views.One that contains THRILLERS and the other DRAMAS,
I did it like this
Expand|Select|Wrap|Line Numbers
  1. create view DRAMAS as
  2. select DIR_NAME, count(GENRE) as DramaCount , DIRECTORS.DIR_ID
  3. from MGENRES, DIRECTORS, DIRECTED_BY
  4. where GENRE = 'Drama' and MGENRES.MOVIE_ID = DIRECTED_BY.MOVIE_ID and DIRECTED_BY.DIR_ID = DIRECTORS.DIR_ID
  5. group by DIR_NAME , DIRECTORS.DIR_ID
  6.  
  7. create view THRILLERS as
  8. select DIR_NAME, count(GENRE) as ThrillerCount , DIRECTORS.DIR_ID
  9. from MGENRES, DIRECTORS, DIRECTED_BY
  10. where GENRE = 'Thriller' and MGENRES.MOVIE_ID = DIRECTED_BY.MOVIE_ID and DIRECTED_BY.DIR_ID = DIRECTORS.DIR_ID
  11. group by DIR_NAME , DIRECTORS.DIR_ID
  12.  
  13.  
  14. select distinct DRAMAS.DIR_NAME, ThrillerCount, DramaCount
  15. from DRAMAS, THRILLERS
  16. where DRAMAS.DIR_ID = THRILLERS.DIR_ID
  17. group by DRAMAS.DIR_NAME , ThrillerCount , DramaCount
  18. order by ThrillerCount desc
  19.  
As you see then i use a select to output what i want.Seems ok now to me.Hope i'm not mistaken :)
Dec 6 '06 #3

gateshosting
P: 25
Glad to help. I think you can do this with one view though. It would group it by Director / Genre, then you can do a select count(*) from myView where genre = x, etc. Once you have the view, you can summarize the data further. The view is like a temporary table. Structured the same way.

Best regards,

Michael C. Gates
Dec 6 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.