Nesting aggregates/subqueries v. derived tables

[RoseM]

Hi again,

I could use some help and have not been able to come to grips with what I need to do to make this work.

I have a table of theories and votes and I want to return the percent to total of 'yes' votes. Here's what I have so far, which doesn't work:

SELECT Count(tentries.vote) AS CountOfvote,count(tentries.theory) as countoftheory
FROM tentries
GROUP BY tEntries.vote, tentries.theory
ORDER BY tEntries.vote,tentries.theory; 

This doesn't work because ultimately I need two things to render the percentage, the total number of votes on the query and the total number of yes votes. This just gives me a breakdown of number of each type of vote for each theory.

What I want is:

The number of yes votes:
select count(tentries.vote) as CountTrueVotes, tentries.theory
From tentries where tentries.vote = true ;

AND....
The count of each theory:
select count(tentries.theory) as CountOfTheories

AND...
The percentage of true votes to total:
Select counttruevotes/countoftheories as OverallYesVotes


Can someone help understand how to nest these together--I've looked into subqueries and derived tables but I'm having a hard time understanding which I need here, and how these fit together.

Very much appreciated!

Thank you!
Rosey

[MMcCarthy]

Try this ...

SELECT DCount("[vote]","tentries", "[vote] = 'yes'") As TotalCount, Count(vote) AS CountOfvote, theory, TotalCount/CountOfvote As PercentVote
FROM tentries
WHERE vote = "yes"
GROUP BY theory;