[SOLVED] Counting uniques .. not as simple as count(distict) ..

[lampstax]

Hey guys, running into a problem again that i cant find an elegant solution to.

 

Lets say I have this dataset:

 

Name    Bday
John     Oct
Mary     March
Jess      April
Mark     Oct
Ben      Oct
Dan      April

 

If i just did a

 

select count(distinct bday) from `db`

 

I would get 3 which is how many unique bday months there are.

 

What I need to get is a count of each bday month.

 

Example:

 

Oct      3

April    2

March  1

 

Any suggestions? Thanks!

[dropfaith]

i cant say this is the most efficent way or elegant way

http://lawrenceguide.org/links/index.php kinda in action here theres only one list type but it works i swear

<?php
// includes
include("../template/conf.php");
// open database connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");
// select database
mysql_select_db($db) or die ("Unable to select database!");
$genres = array();

$q = "SELECT Type FROM links";
$r = mysql_query($q);
$Types = array();
while ( $row = mysql_fetch_row($r) ):

$add = $row[0];

if ( !in_array($add, $Types) ):

$Types[] = $add;

Endif;

Endwhile;

$n = count($Types);

for ($i = 0; $i < $n; $i++):


$Type = $Types[$i];

$query  = "SELECT id FROM links WHERE Type='$Type' ORDER BY id DESC";
$result = mysql_query($query);

$num    = mysql_num_rows($result);

echo '<tr><td><a href="links.php?Type=' . $Type . '">' . $Type . '</a> (<em class="number"> ' . $num . ' </em>)</td></tr>';


Endfor;

?>

[dropfaith]

really tho look up

 

mysql_num_rows

[lampstax]

Yeah, this is somewhat of a `training exercise` for me .. since the system I built only had 3 static codes that I had to count, I just hacked it with 3 select count() statements .. since I read that count() works faster than doing selects, then counting num rows.

 

I guess I am looking for some elegant solution that would in a single query return to me ..

 

Oct -> 3

April -> 2

March -> 1

 

or something to that effect.

[dropfaith]

wow i feel kinda dumb today use group by

http://www.tizag.com/mysqlTutorial/mysqlgroupby.php

select DISTINCT () GROUP BY sOME FIELD

[lampstax]

thanks .. that was easy .. i have so much to learn about this stuff .. and i've been doing it for a minute .. lol

[dropfaith]

np sorry i brain lapsed with the huge ugly code for this haha

[lampstax]

actually .. its still not working ..  sorry to jump to conclusion after improper testing ..

 

the query i made was

 

SELECT DISTINCT bday FROM people GROUP BY bday

 

it seems that would return ..

 

bday
Oct
April
March

 

so a list of distinct bday .. half way there .. now if we can only get a count of how many time each occurs in the db .. we're golden .. lol

 

thanks for the effort though faith .. much appreciated.

[dropfaith]

actually .. its still not working ..  sorry to jump to conclusion after improper testing ..

 

the query i made was

 

SELECT DISTInCT bday, count(bday)  GROUP BY bday  

 

 

you still need to do the count

[lampstax]

Ahh, there it is ... I knew I shouldn't need more than 1 query to get that done!

 

Thanks man.

[fenway]

And you don't need DISTINCT, either.