Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eea61
Starting Member
3 Posts |
 Posted - 2010-06-07 : 21:19:10
|
| I have designed a database for a voting system. I have the following tables:Voter (VoterId,Username,Password)Referendum (ReferendumId,title,description)Question (questionId,referendumId,Questiontxt)Votes (QuestionId,VoterId,Answer,Date)I need to write a query to know how many voters have voted "No" to all questions of a particular referendum. I tries many queries but I couldn't find the correct number. can you help me in this? |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-06-07 : 23:12:15
|
| I feel like it is a homework question..I would be glad to help you provided you show us what are the different select statements you have tried.Regards,BohraI am here to learn from Masters and help new bees in learning. |
 |
|
|
eea61
Starting Member
3 Posts |
Posted - 2010-06-08 : 00:38:35
|
| Select Count(Distinct VoterId) From (Select * from Votes,Question where Votes.questionid = Question.QuestionId)where referendumId=2 and Answer='No';The above query gives the number of users who voted No to a referendum but I dont know how to count only those with all the answers "no". |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2010-06-08 : 01:40:47
|
| [code]select count(distinct a.VoterId) from Votes a where exists(select 1 from Votes b where a.voterid=b.voterid and b.Answer='N')and not exists(select 1 from Votes c where a.voterid=c.voterid and c.Answer<>'N')[/code] |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2010-06-08 : 01:49:11
|
this would be faster,select x.VoterId from(select VoterId,count(distinct Answer) from Votes group by VoterId having count(distinct Answer)=1)xand exists (select 1 from Votes a where x.voterid=a.voterid and a.Answer='N') |
|
|
|
eea61
Starting Member
3 Posts |
Posted - 2010-06-13 : 13:01:41
|
| I was trying to run the query using access which was unsuccessful as access doesn't accept Count(distinct) expression. trying to write it acceptable for access remained unsuccessful. |
|
|
|
|
|
|
|
|
Help in query