5 Replies - 2087 Views - Last Post: 09 October 2013 - 04:53 AM Rate Topic: -----

#1 Yendall  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 08-October 13

Using an ALL operator to compare count(*) values - (SQL)

Posted 08 October 2013 - 01:56 AM

Hi everyone!
This is frustrating me like crazy. I want to compare a count(*) to all other counts, and display the highest value that it finds.

Here is my current SQL:
select directinfo.directorID,directorinfo.firstname,directorinfo.lastname,count(directinfo.mvid)
from  genreinfo, directorinfo, directinfo
where genreinfo.genre = 'Comedy'
and directorinfo.directorID = directinfo.directorID
and directinfo.mvID = genreinfo.mvID
and directorinfo.directorID not in
                      ( select directorID
                        from directorinfo
                        minus
                        select directorid
                        from directorinfo, genreinfo
                        where directinfo.mvID =genreinfo.mvID
                        and genreinfo.genre = 'Comedy'
                        )
group by directinfo.directorID,directorinfo.firstname,directorinfo.lastname
order by count(directinfo.mvid);



I want to compare count(directinfo.mvid) to find the highest count. I've attempted to code things like

HAVING count(directinfo.mvid) > ALL (SELECT count(directinfo.mvid) FROM directinfo))


But i'm not having any luck.

Any help would be appreciated! Thanks!

Is This A Good Question/Topic? 0
  • +

Replies To: Using an ALL operator to compare count(*) values - (SQL)

#2 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 54
  • View blog
  • Posts: 335
  • Joined: 14-November 12

Re: Using an ALL operator to compare count(*) values - (SQL)

Posted 08 October 2013 - 03:16 AM

you can use table joins into your query. here's a good start. Table joins
then just compare the values of each field from and get the highest or you can just sort it.
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5931
  • View blog
  • Posts: 12,853
  • Joined: 16-October 07

Re: Using an ALL operator to compare count(*) values - (SQL)

Posted 08 October 2013 - 03:34 AM

I've never, ever, used ALL. Seriously. Also, minus, not so much. Not sure why that's there.

Ok, you have two fundamental problems to solve.

First, assume you aren't filtering by highest yet. What does the result row look like? I'm assuming the result has a count in it? Make a query to return every row, with count.

Second, using that query as a base, find just the max count.

Now, take the first query and show only the rows where count is equal to max count.
Was This Post Helpful? 0
  • +
  • -

#4 Yendall  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 08-October 13

Re: Using an ALL operator to compare count(*) values - (SQL)

Posted 08 October 2013 - 03:27 PM

View Postbaavgai, on 08 October 2013 - 03:34 AM, said:

I've never, ever, used ALL. Seriously. Also, minus, not so much. Not sure why that's there.

Ok, you have two fundamental problems to solve.

First, assume you aren't filtering by highest yet. What does the result row look like? I'm assuming the result has a count in it? Make a query to return every row, with count.

Second, using that query as a base, find just the max count.

Now, take the first query and show only the rows where count is equal to max count.


This is what it's returning at the moment:

DIRECTORID             FIRSTNAME            LASTNAME             COUNT(DIRECTINFO.MVID) 
---------------------- -------------------- -------------------- ---------------------- 
18                     Charles              Chaplin              5                      
10                     Billy                Wilder               3                      
12                     Brad                 Bird                 2                      
38                     Frank                Capra                2                      
54                     Guy                  Ritchie              2                      
123                    Robert               Zemeckis             2                      
153                    Woody                Allen                2                      
141                    Tim                  Burton               2                      
140                    Terry                Jones                2                      
124                    Roberto              Benigni              1                      
145                    Valerie              Faris                1                      
11                     Bob                  Peterson             1                      
42                     Fred                 Newmeyer             1                      
60                     Howard               Hawks                1                      
86                     Lee                  Unkrich              1                      
87                     Leo                  McCarey              1                      
109                    Pete                 Docter               1                      
1                      Akira                Kurosawa             1                      
15                     Buster               Keaton               1                      
56                     Harold               Ramis                1                      
101                    Norman               Jewison              1                      
45                     Gene                 Kelly                1                      
46                     George               Cukor                1                      
92                     Martin               McDonagh             1                      
135                    Stanley              Kubrick              1                      
142                    Todd                 Phillips             1                      
51                     Gregory              La Cava              1                      
69                     Jean-Pierre          Jeunet               1                      
118                    Robert               Hamer                1                      
133                    Stanley              Donen                1                      
138                    Terry                George               1                      
59                     Henry                Koster               1                      
80                     Jonathan             Dayton               1                      
68                     Jan                  Pinkava              1                      
117                    Rob                  Reiner               1                      
55                     Hal                  Ashby                1                      
50                     Giuseppe             Tornatore            1                      
48                     George               Hill                 1                      
6                      Andrew               Stanton              1                      
129                    Sam                  Taylor               1                      
99                     Mike                 Nichols              1                      
77                     John                 Lasseter             1                      
70                     Joel                 Coen                 1                      
22                     Clyde                Bruckman             1                      
24                     Danny                Boyle                1                      




I'm not entirely sure whether that count is correct or not, i'll have to go through the records manually. I'll work on implementing that Max Count sub-query today and see how I go.

Do you think this is returning correct count values?
Thanks for your help!
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5931
  • View blog
  • Posts: 12,853
  • Joined: 16-October 07

Re: Using an ALL operator to compare count(*) values - (SQL)

Posted 09 October 2013 - 04:16 AM

Get the count at the simplest level. So, count(directinfo.mvid).

Now, step back, what are we really looking for? It looks like movies of genre 'Comedy' are your first search criteria. So:
select mvID from genreinfo where genre = 'Comedy'



Should be a list of all comedy movie ids.

Now, we want to directors for those movies.
select a.directorid
	from directorinfo a
		inner join genreinfo b
			on a.mvID = b.mvID
				and b.genre = 'Comedy'



Note, the above is the preferred join syntax, but it should be identical to your:
select directorid
	from directorinfo, genreinfo
	where directinfo.mvID =genreinfo.mvID
		and genreinfo.genre = 'Comedy'



So, we have a list of directorid in comedy movies. NOW we count them:
select a.directorid, count(*) as MovieCount
	from directorinfo a
		inner join genreinfo b
			on a.mvID = b.mvID
				and b.genre = 'Comedy'
	group by a.directorid




Getting the directorid with the highest movie count is tricky. You must first get the highest count, then go back to your first query. So, highest:
select max(count(*)) as MaxMovieCount
	from directorinfo a
		inner join genreinfo b
			on a.mvID = b.mvID
				and b.genre = 'Comedy'
	group by a.directorid



Note, we keep the group by. This will return a single result. To get the directorid ( or ids ) with the highest, we need to feed this value back in:
select a.directorid, count(*) as MovieCount
	from directorinfo a
		inner join genreinfo b
			on a.mvID = b.mvID
				and b.genre = 'Comedy'
	group by a.directorid
	having count(*) = (
		select max(count(*))
			from directorinfo a
				inner join genreinfo b
					on a.mvID = b.mvID
						and b.genre = 'Comedy'
			group by a.directorid
	)



Now, back to the beginning:
select a.directorID, a.firstname, a.lastname, b.MovieCount
	from directorinfo a
		inner join (
			select a.directorid, count(*) as MovieCount
				from directorinfo a
					inner join genreinfo b
						on a.mvID = b.mvID
							and b.genre = 'Comedy'
				group by a.directorid
				having count(*) = (
					select max(count(*))
						from directorinfo a
							inner join genreinfo b
								on a.mvID = b.mvID
									and b.genre = 'Comedy'
						group by a.directorid
				)
		) b on a.directorid=b.directorid



Note, you can double check your prior query with this:
select a.directorID, a.firstname, a.lastname, b.MovieCount
	from directorinfo a
		inner join (
			select a.directorid, count(*) as MovieCount
				from directorinfo a
					inner join genreinfo b
						on a.mvID = b.mvID
							and b.genre = 'Comedy'
				group by a.directorid
		) b on a.directorid=b.directorid
	order by b.MovieCount desc



Well, that's the whole answer, I'm afraid. Rather than being cryptic, I thought it important to show the logical steps of how you worry through these problems.

Hope this helps.
Was This Post Helpful? 1
  • +
  • -

#6 Yendall  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 08-October 13

Re: Using an ALL operator to compare count(*) values - (SQL)

Posted 09 October 2013 - 04:53 AM

Thank you so much for your thorough answer, I understand how this is working now!
Because I wasn't actually finding that maximum value after finding count, so trying to find comparisons between count values was pretty pointless. But I see how that works now, thanks heaps!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1