[Resolved] MySql SELECT DISTINCT and COUNT

[zdislaw]

I'm building a list of how many times a distinct publication is listed in my database. I want to include a count of how many times each publication is listed. I'm pretty sure I need to use COUNT in my SELECT statement somewhere, but am having a hard time figuring out how.

<?php

$sql = "SELECT DISTINCT publication FROM $table ORDER BY source ASC";
$result = mysql_query ($sql) or die (mysql_error());
$numrows = mysql_num_rows ($result);

while ($myrow = mysql_fetch_array($result))
  {
	printf ("%s", $myrow["publication"]);
  }

?>

I want the result to render like this:

Publication1 (23)
Publication2 (1)
Publication3 (45)

Probably printed like:

printf ("%s (%s)", $myrow["publication"], $myrow["publicationCount"]);

Thanks!

[sledgeweb]

You want to try something like this:

SELECT DISTINCT publication, COUNT(publication) AS count FROM table1 GROUP BY publication

This will return two columns, use your fetch row with [0] and [1] to retrieve the data.

[bad76]

Hi,
Count give after the SELECT word before the FROM...

SELECT DISTINCT publication, COUNT(DISTINCT publication) as num_of FROM $table...

You can access with $row["num_of"].

I hope this helps.

[zdislaw]

Ahhh....in looking at COUNT, I neglected GROUP. This oughtta work.

  <?
$sql="SELECT DISTINCT source, COUNT(*) as total FROM $table GROUP BY source"; 

$result2 = mysql_query ($sql); 
while (list($source,$total) = mysql_fetch_row($result2)) { 
    echo " $source (" . number_format($total,0) . ")<br>"; 
}
?>

[sledgeweb]

Looks good. You can add your ORDER BY back in there if you wish.

[zdislaw]

That did it. Thanks guys!