Jump to content


Photo

counting total recs for each state?


  • Please log in to reply
3 replies to this topic

#1 cgchris99

cgchris99
  • Members
  • PipPipPip
  • Advanced Member
  • 37 posts

Posted 12 July 2003 - 09:20 PM

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

#2 Avalanche

Avalanche
  • Members
  • PipPipPip
  • Advanced Member
  • 73 posts

Posted 12 July 2003 - 11:55 PM

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.
DEFINE("YOU","Spending Too Much Time On The Computer");

#3 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 13 July 2003 - 12:07 AM

if you have multiple state entries use:

select count(*), state from table group by state
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#4 Avalanche

Avalanche
  • Members
  • PipPipPip
  • Advanced Member
  • 73 posts

Posted 13 July 2003 - 12:24 AM

Oh, whoops... I missread the question. He wanted to know how many records have that state... sorry, heheheh... :?
DEFINE("YOU","Spending Too Much Time On The Computer");




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users