Jump to content

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/207517-count-using-group-by/
Share on other sites

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.

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.