MYSQL COUNT() with joined tables?

Portal Home > Knowledgebase > Articles Database > MYSQL COUNT() with joined tables?

Posted by lexington, 08-26-2008, 06:51 AM
Hello, when I use COUNT() in a mysql query with two joined tables, instead of displaying the total count it displays 1 count per row. Here is my query: There are 4 rows, so instead of displaying: it displays: Is there a way to make it work with joined tables? Thanks.
Posted by blueroomhosting, 08-26-2008, 08:05 AM
Nothing to do with joined tables. The group by clause is causing the song_ids to be grouped, and since COUNT is an aggregate function, it will operate on each of the groups created by the GROUP BY rather than the result set as a whole. If you want each song to only count once, why not ditch the GROUP BY clause and use DISTINCT, like this:
Posted by sasha, 08-26-2008, 08:07 AM
edit> too slow
Posted by lexington, 08-26-2008, 08:08 AM
Thanks it worked I only used GROUP BY due to the mysql error so I thought it was necessary when using COUNT with joined tables but you have explained that it is not. Thanks again
Posted by lexington, 09-07-2008, 11:40 AM
You know the funny thing is that I had to come across using a query like this again and I used your working example like I did the last time but I get the illegal group by mysql error: mysql says: Kinda weird how this doesn't work but the other one does. I basically want it to count how many times the band_id shows up from the comments table, and select the com_limit from the main band table. I can use two separate queries and it works but I would think that it takes longer: Last edited by lexington; 09-07-2008 at 11:48 AM.