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

--
--Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com

"C White" <cw****@theatomicmoose.ca> wrote in message
news:rf********************@rogers.com...

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

[C White]

Mark Schupp wrote:

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]

"C White" <cw****@theatomicmoose.ca> wrote in message
news:rf********************@rogers.com...

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

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.807 / Virus Database: 549 - Release Date: 12/7/2004

[Des Perado]

"Hal Rosser" <hm******@bellsouth.net> wrote in message
news:TO***************@bignews6.bellsouth.net...

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]

"Des Perado" <de*@per.ado> wrote in message
news:32*************@individual.net...

"Hal Rosser" <hm******@bellsouth.net> wrote in message
news:TO***************@bignews6.bellsouth.net...

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?

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

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.817 / Virus Database: 555 - Release Date: 12/15/2004

[C White]

Hal Rosser wrote:

"Des Perado" <de*@per.ado> wrote in message
news:32*************@individual.net...

"Hal Rosser" <hm******@bellsouth.net> wrote in message
news:TO***************@bignews6.bellsouth.net. ..

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?

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

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.817 / Virus Database: 555 - Release Date: 12/15/2004

It's all good to me :)