counting total recs for each state?

[cgchris99]

I have a bunch of event records for various event dates.

 

I want to write an select state that shows the following

 

State Count

AZ 50

AR 2

AK 450

CO 75

CT 92

 

etc.

 

sorted by state of course.

 

How do I do this.

 

thanks for any help

[Avalanche]

What I would use is:

 

[php:1:c0bb7aeddb]$query = mysql_query(SELECT * FROM table ORDER BY state DESC);

 

while($r=mysql_fetch_array($query)) { // cycle through states

 

$state=$r[\"state\"]; // turn row variable to regular variable

$count=$r[\"count\"]; // turn row variable to regular variable

 

echo \"$state $count<br>n\"; // display state and count

 

}[/php:1:c0bb7aeddb]

 

That should loop through each different one displaying the state and count (assuming your column names are state and count... otherwise you need to change the $r[\"columnhere\"]).

 

If it ends up in the reverse direction of what you want then change the DESC to ASC in the query.

 

Good luck.

[effigy]

if you have multiple state entries use:

 

select count(*), state from table group by state

[Avalanche]

Oh, whoops... I missread the question. He wanted to know how many records have that state... sorry, heheheh... :?