COUNT using GROUP BY

[lemmin]

If I have a table 'actors' that looks like this:

ActorID   MovieID
1             1
2             1
3             2
4             3

(With Actor as a primary key.)

 

If I do the following query:

SELECT COUNT(ActorID) FROM actors

It will return the number of rows in the table.

 

If I do this query:

SELECT COUNT(MovieID) FROM actors GROUP BY MovieID

I would expect it to return the number of different movies that are related to in the table. Instead it returns one row for each movie that is related with the COUNT actually being the number of actors.

 

How can I get this query to return a number of referenced MovieIDs?

 

Thanks

[Mchl]

SELECT COUNT(*) FROM (SELECT DISTINCT MovieID FROM actors) AS sq

[lemmin]

I was hoping there was a way to do it without a subquery. Do you think that is possible?

 

Thanks for the help.

[Mchl]

Maybe. But why don't you like a subquery?

[lemmin]

Because I want to return more than just that one number and I would expect it to be faster without the subquery.

[Mchl]

What are you basing your expectations on?

 

SELECT BENCHMARK(100000,(SELECT SQL_NO_CACHE COUNT(*) FROM (SELECT DISTINCT MovieID FROM actors) AS sq));

 

this will run the query 100000 times. See if it's fast enough for you.

[lemmin]

It isn't really whether it is fast enough for me, but efficient enough. Because I was able to return the result that I wanted IF it was the primary key, I assumed that there was a simple way to get the same result on a different field in the table. I will probably just use the subquery, but I was wondering if anyone knew if there was a workaround to COUNT returning different results based on whether or not the field was a primary key.

 

Thanks again for the help.

[Mchl]

Well... COUNT on primary key column is a number of rows in the table. In MyISAM tables you get it extremely fast, because this value is stored alongside the table and is not calculated. In any other column the values might not be unique, so the actual COUNT operation must be performed.