how to count distinct elements, MySQL

Codewalkers Forums Sponsor:

July 23rd, 2003, 08:40 PM

mugane

Join Date: Apr 2007

Location: Charlottesville, VA USA

Posts: 425

Time spent in forums: < 1 sec
Reputation Power: 2

how to count distinct elements, MySQL

Hi,

My problem has to do with counting records in a table. I would like to end up with an associative array $count, where each index in $count is the field value and each respective value in $count is the number of occurrences of that value in the table.

To clarify, say I have a field "category", whose value must be one of several options (say "one", "two", or "three"). I do not know how many records there are in the table, there could be any number. Also, there could be any number of options for the "category" field, so a dynamic approach is necessary.

This is what I have so far:

php Code:

Original - php Code
<? $query = "SELECT count(*) FROM tablename"; $result = mysql_fetch_array(mysql_query($query)); $count = $result[0]; // this just gets the total number of records ?>

<?
$query = "SELECT count(*) FROM tablename";
$result = mysql_fetch_array(mysql_query($query));
$count = $result[0]; // this just gets the total number of records
?>

I also know that using "SELECT count(DISTINCT category) FROM tablename" will yield the total number of distinct categories, but will not reveal the number of occurrences of each distinct category.

If anyone has any ideas about how to generate this information in an efficient manner please let me know.

Thanks,

Peter

July 23rd, 2003, 09:28 PM

brut

Join Date: Apr 2007

Posts: 367

Time spent in forums: 3 m 30 sec
Reputation Power: 2

RE: how to count distinct elements, MySQL

This query should work:

Code:

SELECT DISTINCT category,COUNT(category) AS cat_num FROM tablename GROUP BY category;

July 23rd, 2003, 11:43 PM

honcho

Contributing User

Codewalkers Beginner (1000 - 1499 posts)

Join Date: Apr 2007

Location: Cape Cod

Posts: 1,347

Time spent in forums: 3 h 52 m 2 sec
Reputation Power: 3

RE: how to count distinct elements, MySQL

brut's query should work, but there's more in there than you need (DISTINCT + GROUP BY is a little redundant). This is simplier:

Code:

SELECT category, COUNT(*) AS cat_num FROM tablename GROUP BY category

July 24th, 2003, 12:09 PM

mugane

Join Date: Apr 2007

Location: Charlottesville, VA USA

Posts: 425

Time spent in forums: < 1 sec
Reputation Power: 2

RE: how to count distinct elements, MySQL

Thanks a heap for the replies, much appreciated.

This is what I ended up with:

php Code:

Original - php Code
<? $query = "SELECT columnname,count(*) AS col_num FROM tablename GROUP BY columnname ASC"; $result = mysql_query($query); while($row = mysql_fetch_array($result,MYSQL_ASSOC)) { while(list($key, $value) = each($row)) { if($key!="col_num") $name = $value; else $count[$name]=$value; } } while(list($key, $value) = each($count)) echo "$key = $value<br>"; ?>

<?
$query = "SELECT columnname,count(*) AS col_num FROM tablename GROUP BY columnname ASC";
$result = mysql_query($query);
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
while(list($key, $value) = each($row)) 
{ 
if($key!="col_num") $name = $value;
else $count[$name]=$value;
}
}
while(list($key, $value) = each($count)) echo "$key = $value<br>"; 
?>

echoes:

Code:

computersgeneral = 4
computershardware = 2

This works, but I think it looks horribly inefficient (two returned values in $result where only one is necessary) - do you know a better way to do it (e.g. a query that returns an associative array with [index == the column value] and [value == number of occurrences of column value])?

Thank you very much,

Peter

July 24th, 2003, 01:14 PM

honcho

Contributing User

Join Date: Apr 2007

Location: Cape Cod

Posts: 1,347

Time spent in forums: 3 h 52 m 2 sec
Reputation Power: 3

RE: how to count distinct elements, MySQL

$result is not an array, it's a mysql result identifier. You need to use mysql_fetch_array to get the next row of data from it.

php Code:

Original - php Code
$query = "SELECT category, COUNT(*) AS cat_num FROM tablename GROUP BY category"; $result = mysql_fetch_array(mysql_query($query),MYSQL_ASSOC) ; while($row = mysql_fetch_array($result)) { echo "$row[category] = $row[cat_num]<br>"; }

 
$query = "SELECT category, COUNT(*) AS cat_num FROM tablename GROUP BY category";
$result = mysql_fetch_array(mysql_query($query),MYSQL_ASSOC)  ;
while($row = mysql_fetch_array($result))
{
echo "$row[category] = $row[cat_num]<br>";
}

July 24th, 2003, 01:16 PM

honcho

Contributing User

Join Date: Apr 2007

Location: Cape Cod

Posts: 1,347

Time spent in forums: 3 h 52 m 2 sec
Reputation Power: 3

RE: how to count distinct elements, MySQL

Ignore most what what I just posted. I must be drinking.

You need to repeatedly use mysql_fetch_array. The way you had it originally, you just got the first row returned.

php Code:

Original - php Code
$query = "SELECT category, COUNT(*) AS cat_num FROM tablename GROUP BY category"; $result = mysql_query($query); while($row = mysql_fetch_array($result)) { echo "$row[category] = $row[cat_num]<br>"; }

 
$query = "SELECT category, COUNT(*) AS cat_num FROM tablename GROUP BY category";
$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{
echo "$row[category] = $row[cat_num]<br>";
}

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: