lemmin Posted July 12, 2010 Share Posted July 12, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/207517-count-using-group-by/ Share on other sites More sharing options...
Mchl Posted July 12, 2010 Share Posted July 12, 2010 SELECT COUNT(*) FROM (SELECT DISTINCT MovieID FROM actors) AS sq Quote Link to comment https://forums.phpfreaks.com/topic/207517-count-using-group-by/#findComment-1084907 Share on other sites More sharing options...
lemmin Posted July 12, 2010 Author Share Posted July 12, 2010 I was hoping there was a way to do it without a subquery. Do you think that is possible? Thanks for the help. Quote Link to comment https://forums.phpfreaks.com/topic/207517-count-using-group-by/#findComment-1084910 Share on other sites More sharing options...
Mchl Posted July 12, 2010 Share Posted July 12, 2010 Maybe. But why don't you like a subquery? Quote Link to comment https://forums.phpfreaks.com/topic/207517-count-using-group-by/#findComment-1084920 Share on other sites More sharing options...
lemmin Posted July 12, 2010 Author Share Posted July 12, 2010 Because I want to return more than just that one number and I would expect it to be faster without the subquery. Quote Link to comment https://forums.phpfreaks.com/topic/207517-count-using-group-by/#findComment-1084942 Share on other sites More sharing options...
Mchl Posted July 12, 2010 Share Posted July 12, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/207517-count-using-group-by/#findComment-1084956 Share on other sites More sharing options...
lemmin Posted July 12, 2010 Author Share Posted July 12, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/207517-count-using-group-by/#findComment-1084974 Share on other sites More sharing options...
Mchl Posted July 12, 2010 Share Posted July 12, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/207517-count-using-group-by/#findComment-1084975 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.