How to count the most common value in SQL?

Question

At the moment I've got the following code:

$forummost = mysql_query("

 SELECT door, 
 COUNT(door) AS doorCount 
 FROM forum_posts 
 GROUP BY door 
 ORDER BY COUNT(door) 
 DESC

");
$forummostc = mysql_num_rows($forummost);
$forummostf = mysql_fetch_assoc($forummost);

Can somebody explain me why mysql_num_rows($forummost) doesn't work?

Thanks in advance!

I get "227" but when I SELECT the same user in PHPMyAdmin SQL, I get: "1158" — Mossawi, Apr 10 '12 at 19:29
The number of rows in your return array is the number of distinct doors. — AJ., Apr 10 '12 at 19:30
@Mossawi: can you edit your question and post expected and actual output there? — Salman A, Apr 10 '12 at 19:32

score 1 · Answer 1 · answered Apr 10 '12 at 19:36

1

mysql_num_rows counts the number of rows returned by the query. Your query is what is the count for each door so only one row for each door is returned.

answered Apr 10 '12 at 19:36

webbiedave

48,414
8
88
101

score 1 · Accepted Answer · answered Apr 10 '12 at 19:41

1

GROUP BY selects only the different values of that column in mysql. In your case, as pointed out, it selects as many rows as different door values are there.

answered Apr 10 '12 at 19:41

Francisco Presencia

8,732
6
46
90

score 0 · Answer 3 · answered Apr 10 '12 at 19:43

try this, I tested, it works..

<?php 
$link = mysql_connect('localhost','root',''); 
if (!$link) 
{ 
    die('Could not connect to MySQL: ' . mysql_error()); 
} 
echo 'Connection OK'."</br>"; 


$forummost = mysql_query("
    SELECT door, 
COUNT(door) AS doorCount 
FROM forum_posts 
GROUP BY door 
ORDER BY COUNT(door) 
DESC
");


$forummostc = mysql_num_rows($forummost);

if ($forummostc == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

while ($row = mysql_fetch_assoc($forummost)) {
    echo $row["door"];
    echo $row["doorCount"];

}

mysql_free_result($forummost);

mysql_close($link); 

?>

How to count the most common value in SQL?

3 Answers3