How to calculate the yes percent

[lovedieer]

I have a query which included person's ID, name, DOB, and the answer (Yes/No/Null) for a question. I want to count the Yes Number , Total Number of Respond, and the percentage (Yes Number/ Total Reponded number), How can I do it.

Thanks.

 

[ajetrumpet]

you can use COUNT(), but the problem with that is that every other field has to be GROUPED. if you want it all in one query though, there is nothing wrong with using a DSUM() function, although the single answer you get from that will appear in every record in the field. as I said, there is nothing wrong with this, if you're using that field on a report as a calculated value comming from a source. if you're using the query as a base for your report though, it will not be good, because you will see a bunch of records with the same number in one of the fields

I would go this route actually, especially if you are not showing the query to any user directly.

 

[dcb]

I was thinking something like:
SELECT Count(Table1.Question) AS CountOfQuestion, Sum(IIf([Question]=True,1,0)) AS YesCount, Sum(IIf([Question]=True,1,0))/Count(Table1.Question)*100 AS PerCount
FROM Table1;
Sorry used the word question not answer

It would give you:
6 | 3 | 50

as ajetrumpet says depends on usage....

 

[ajetrumpet]

I was thinking something like:
SELECT Count(Table1.Question) AS CountOfQuestion, Sum(IIf([Question]=True,1,0)) AS YesCount, Sum(IIf([Question]=True,1,0))/Count(Table1.Question)*100 AS PerCount
FROM Table1;
Sorry used the word question not answer

It would give you:
6 | 3 | 50

as ajetrumpet says depends on usage....

that would not give you that result. You need the GROUP BY clauses in there. Aggregates are a pain in the a$$ to work with, and most of the time (in my view), domain aggregates are better anyway. They don't have the restrictions that simple aggregates do.

 

[dcb]

Also see you have Yes,No,Null in the OP
Assume this is then text not Boolean - IE you would use 'Yes',1,0

ajetrumpet - It ran on mine without the Group by clauses -Assume its because its all COUNT

 

[Brianwarnock]

What are you supposed to Group on , all of the Select are aggregates

Brian

 

[ajetrumpet]

ajetrumpet - It ran on mine without the Group by clauses -Assume its because its all COUNT

sorry about that! You only to group them if you have regular fields included with your SELECT statement. I see you have all expressions, so that's a different story. Remember, when you're talking to me about anything, I don't really know anything, so you have to make your clarification CLEAR, because I'm always wrong.