counting question

[C White]

i have been searching online for a few days now, and I can't seem to
find the answer for what I am looking for, maybe I just don't know how
to phrase it properly for the search engines...

at any rate say I have a database with the following records


john
david
john
john
al
ed
tom
tom


what I am looking for a is tutorial/lesson that eplains on how i can
write an asp script that will autmatically count these for me and return
the following:

john 3
tom 2
david 1
al 1
ed 1

(total the amount of times the name shows up and sort it in descending order

now I know I can do

"Select Count(*) AS process FROM table WHERE name='john';"

and this will tell me that john shows up 3 times, but I don't want to
have to code this for everyname, so I am looking for a
lesson/tuturial/example that will look at the name colum, figure out all
the names which show up and then count them before sorting them out

i think you can see why i am having a problem searching for this...

if anyone can point me towards a link that helps to get me going on
this, or even the correct phrase to use when searching for it would help
also, please keep in mind i'm not a programmer i'm just learning this
for my own interest which is why i would prefer to find a
lesson/tutorial that explains what is happening

thanks

 

[Mark Schupp]

Not sure this is the exact syntax but:

select personname, count(personname) from yourtable group by personname

 

[C White]

Not sure this is the exact syntax but:

select personname, count(personname) from yourtable group by personname

that was great!!! it was so simple yet i had a hard time figuring it out
until you posted your sample, what i did was

select Name, count(Name) AS namecount from Table group by Name

and it worked

thanks!!!!

 

[Hal Rosser]

i have been searching online for a few days now, and I can't seem to
find the answer for what I am looking for, maybe I just don't know how
to phrase it properly for the search engines...

at any rate say I have a database with the following records


john
david
john
john
al
ed
tom
tom


what I am looking for a is tutorial/lesson that eplains on how i can
write an asp script that will autmatically count these for me and return
the following:

john 3
tom 2
david 1
al 1
ed 1

(total the amount of times the name shows up and sort it in descending order

now I know I can do

"Select Count(*) AS process FROM table WHERE name='john';"

and this will tell me that john shows up 3 times, but I don't want to
have to code this for everyname, so I am looking for a
lesson/tuturial/example that will look at the name colum, figure out all
the names which show up and then count them before sorting them out

i think you can see why i am having a problem searching for this...

if anyone can point me towards a link that helps to get me going on
this, or even the correct phrase to use when searching for it would help
also, please keep in mind i'm not a programmer i'm just learning this
for my own interest which is why i would prefer to find a
lesson/tutorial that explains what is happening

thanks

If you throw an "OREDR BY " clause into the SQL, it would make things go a
lot easier (Order By name)
then just treat it like a simple control-break

psuedo-code for ya......
set counter to 0
read a name into a hold field (primer read)
'//**then start the loop
do while not rs.eof
read next name
if its the same as in the hold field
increment the counter
else
print the name and the value of the counter
set counter to 0
set the hold filed to the new name
end if
Loop

 

[Des Perado]

psuedo-code for ya......
set counter to 0
read a name into a hold field (primer read)
'//**then start the loop
do while not rs.eof
read next name
if its the same as in the hold field
increment the counter
else
print the name and the value of the counter
set counter to 0
set the hold filed to the new name
end if
Loop

This is a joke, right?

 

[Hal Rosser]

This is a joke, right?

No - but I liked Mark's solution better, too.
Thanks for the kind words

 

[C White]

No - but I liked Mark's solution better, too.
Thanks for the kind words

It's all good to me