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:

$page_query = "SELECT COUNT(a.song_id)
FROM " . CHARTS_TABLE . " a, " . SONGS_TABLE . " b
WHERE a.song_id = b.song_id
AND b.total_hidden IS NULL
AND b.chart_hide IS NULL
GROUP BY a.song_id";

There are 4 rows, so instead of displaying:
COUNT(a.song_id)
4
it displays:

COUNT(a.song_id)
1
1
1
1

Is there a way to make it work with joined tables? Thanks.

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:

$page_query = "SELECT COUNT(DISTINCT(a.song_id))
FROM " . CHARTS_TABLE . " a, " . SONGS_TABLE . " b
WHERE a.song_id = b.song_id
AND b.total_hidden IS NULL
AND b.chart_hide IS NULL";

<edit> too slow </edit>

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 :)

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:

$sql7 = "SELECT COUNT(DISTINCT(a.band_id)), b.com_limit
FROM " . COMMENTS_TABLE . " a, " . BANDS_TABLE . " b
WHERE a.band_id = b.band_id
AND b.band_id = '".$com_row['band_id']."'";

mysql says:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

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:


$sql7 = "SELECT COUNT(band_id) AS max_coms
FROM " . COMMENTS_TABLE . "
WHERE band_id = '".$com_row['band_id']."'";
$res7 = query_db($sql7);
$row7 = mysql_fetch_array($res7);

$sql8 = "SELECT com_limit FROM " . BANDS_TABLE . "
WHERE band_id = '".$com_row['band_id']."'";
$res8 = query_db($sql8);
$row8 = mysql_fetch_array($res8);