467,144 Members | 1,190 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,144 developers. It's quick & easy.

counting question part 2

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
Jul 22 '05 #1
  • viewed: 1077
Share:
7 Replies
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"
Jul 22 '05 #2
sorry... I am using an access 2000 database

I tried your example and it gives me an error on this line:

SUM (Iif(answer=0,1,0) As answercount

so i change it to:

SUM If(answer=0,1,0) As answercount

which still gives a missing operator error, however this does give me
something to work with, in the meantime if i come up with an answer i'll
let you know, but any more hints/suggestions are appreciated

thanks


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 22 '05 #3
C White wrote:
sorry... I am using an access 2000 database

I tried your example and it gives me an error on this line:

SUM (Iif(answer=0,1,0) As answercount
What error??? It works fine in my database

so i change it to:

SUM If(answer=0,1,0) As answercount obviously that won't work. "if" is a VBA keyword, not a function. The
function is "iif"


--
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"
Jul 22 '05 #4
Bob Barrows [MVP] wrote:
C White wrote:
sorry... I am using an access 2000 database

I tried your example and it gives me an error on this line:

SUM (Iif(answer=0,1,0) As answercount

Oh wait! I left out the closing parenthesis. It should be
SUM (Iif(answer=0,1,0)) As answercount

Bob Barrows

--
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"
Jul 22 '05 #5
thanks for cluing me in on "Iif"

I added the closing bracket and the error I get is:

Data type mismatch in criteria expression.

could it be due to the fact that the answer field in my table is text?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 22 '05 #6
C White wrote:
thanks for cluing me in on "Iif"

I added the closing bracket and the error I get is:

Data type mismatch in criteria expression.

could it be due to the fact that the answer field in my table is text?

Umm, yes. I was under the impression that it was a boolean (Yes/No) field.

Just change it to:

SUM (Iif(answer='No',1,0)) As answercount
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #7
Great, that worked

Thanks a lot :)

Your answers also led me to do a bit or reading about Iif as well :)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 22 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Srinath Avadhanula | last post: by
4 posts views Thread by Victor Engmark | last post: by
1 post views Thread by Tony Johansson | last post: by
1 post views Thread by Tony Johansson | last post: by
3 posts views Thread by Megan | last post: by
5 posts views Thread by chrisc@cemgraft.co.uk | last post: by
18 posts views Thread by ChadDiesel | last post: by
1 post views Thread by oec.deepak@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.