C White wrote:
A little while back I had a question about counting records and my
example was something like this:
in my database table i have the following records
john
ed
john
rick
tom
ed
john
and i wanted to know how to count the records so i could display it as
3 john
2 ed
1 rick
1 tom
my end solution, with help from the newsgroup readers was:
select Name, count(Name) AS namecount from Table group by Name
now i want to take it a step further
the table looks like this:
john yes
ed no
john no
rick no
tom yes
ed yes
john no
and i would like to display it this way using asp
3 john 2 no
2 ed 1 no
1 rick 1 no
1 tom 0 no
the yes/no is stored as plain text and I know that if i just want to
count the number of times in total it appears i would do something
like
select, count(*) AS answer from Table WHERE answer='no'
and i would get something like
3 no total
but how do i combine what i know to get it to display:
3 john 2 no
2 ed 1 no
1 rick 1 no
1 tom 0 no
thanks
You did not mention what type and version of database you are using* so it
is difficult to get specific. Here is a solution that will work in Access:
select Name, count(Name) AS namecount,
SUM(Iif(answer=0,1,0) As answercount
from Table group by Name
Bob Barrows
*Please remember to tell us what database you are using so we don't waste
time with irrelevant solutions
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
