472,805 Members | 926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

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
7 1225
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Srinath Avadhanula | last post by:
Hello, I am wondering if there is a way of counting graphemes (or glyphs) in python. For example, in the following string: u'\u0915\u093e\u0915' ( or equivalently, u"\N{DEVANAGARI LETTER...
4
by: Victor Engmark | last post by:
When looking for a method to fetch unique elements and counting the number of occurences of each of them, I found quite a lot of gross examples of complex XSL. But after realizing the subtle...
1
by: Tony Johansson | last post by:
Hello Experts! I reading a book called programming with design pattern revealed by Tomasz Muldner and here I read something that I don't understand completely. It says "A garbarage...
1
by: Tony Johansson | last post by:
Hello Experts! I reading a book called programming with design pattern revealed by Tomasz Muldner and here I read something that I don't understand completely. It says "A garbarage...
3
by: Megan | last post by:
hi everybody- i'm having a counting problem i hope you guys and gals could give me some help with. i have a query that retrieves a bevy of information from several different tables. first let...
5
by: chrisc | last post by:
Hello, Hope this is the right place for this... I am creating a testing database for components as they come off a production line. My reports need to select faults that are found, as well...
18
by: ChadDiesel | last post by:
I appreciate the help on this group. I know I've posted a lot here the last couple of weeks, but I was thrown into a database project at my work with very little Access experience. No other...
14
by: Dan | last post by:
Is this discouraged?: for line in open(filename): <do something with line> That is, should I do this instead?: fileptr = open(filename) for line in fileptr: <do something with line>
1
by: oec.deepak | last post by:
Hi Cn any one telll me what is Reference counting in C++.
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.