Getting number of rows returned by a query

When I do a COUNT/GROUP BY in a query, then a mysql_num_rows(), they return the same number instead of the telescoped number and full number of rows. Right now I run the query twice, first for the COUNT/GROUP BY to get the grouped rows, then again to get the full row count by leaving off the COUNT/GROUP BY:

$sqlqueryc = "SELECT tier_level3_brand, COUNT(*) AS Number
	FROM cover_tiers
	GROUP BY tier_level3_brand
$sqlc = mysql_query($sqlqueryc);

// Count number of rows returned by query
$sqlqueryac = "SELECT tier_level3_brand
	FROM cover_tiers
$sqlac = mysql_query($sqlqueryac);
$countc = mysql_num_rows($sqlac);

Is there a more efficient way than running a query twice like this?


If this is a mysql question, then probably best asking in that forum, if it is a php question, then does :

echo count($sqlac);

do it for you? Sorry if I have misunderstood your Q.

I did it like this and it did not work:

echo “<p>Above based on " . count($sqlc) .” issues.</p>"; It returned quantity of 1 rather than 8.

You are right; this may be a MySQL question.

Maybe you could use a MySQL subquery to combine the two queries? Something like;

$sqlqueryc = "SELECT tier_level3_brand, COUNT(*) AS Number, (SELECT COUNT(*) FROM cover_tiers WHERE 1) AS totalcount
              FROM cover_tiers
              WHERE 1 
              GROUP BY tier_level3_brand
              ORDER BY Number DESC";

You could then use totalcount for your full row count.

I think what your after is rollup.

Thanks oddz, I had never come across that modifier before. :slight_smile: