1

Suppose I have a table of election data, call it ELECTIONS, with one row per voter per election, like so:

VoterID ElectionID
A           1
A           2
B           1
C           2
D           3
E           1
E           2

I want to know the number of voters who voted both in election 1 and in election 2; I don't care about anyone else. The number should be 2 (voter A and Voter E).

Would something like this work:

select count(Elections) as NumVoters
from (
select VoterID, ElectionID, count(ElectionID) as Elections
from ELECTIONS
where ElectionID=1 or ElectionID=2
group by VoterID
having (count(ElectionID)=2)
) x;

UPDATE: This is my first question here, and I am blown away at how helpful and fast folks have been. I revised the query above to fix the lack of an alias at the end and to add a terminating semicolon.

THANK YOU!

Improve this question
6
  • Does it work in your fictional dataset? Commented Jan 20, 2014 at 19:58
  • I'm not at my fictional dataset right now. I'm trying to think it through. This seemed like something that would be easy for a SQL person (I'm just learning). Commented Jan 20, 2014 at 20:01
  • what's wrong with your query? you could fix it a little, but it looks okay.... Commented Jan 20, 2014 at 20:09
  • Yes, what you have will work, with one caveat regarding potential for duplicate (VoterID, ElectionID) tuples. If you have a unique constraint on (VoterID, ElectionID), then your query will work fine. Commented Jan 20, 2014 at 20:15
  • Thank you! One question about the "unique constraint on (VoterID, ElectionID)": Are you saying I need to set a unique constraint on them, or just that they need to in fact be unique? They are unique, but I haven't explicitly defined the pair as a key. Commented Jan 20, 2014 at 20:18

5 Answers 5

Reset to default
2

Yes. what you have should work. (You will need to add an alias on the derived table, the error messsage you get should be self explanatory. Easy to fix, just add a space and the letter c (or whatever name you want) at the end of your query.

There's one caveat regarding the potential for duplicate (VoterID, ElectionID) tuples.

If you have a unique constraint on (VoterID, ElectionID), then your query will work fine.

If you don't have a unique constraint (which disallows duplicate (VoterID, ElectionId)), then there's a potential for a voter with two (2) rows for ElectionID 1, and no rows for ElectionID 2... for that voter to get included in the count. And a voter that voted twice in ElectionID 1 and only once in ElectionID 2, that voter will be excluded from the count.

Including the DISTINCT keyword inside a COUNT would fix that problem, e.g.

HAVING COUNT(DISTINCT ElectionID) = 2

I'd write the query differently, but what you have will work.

To get the count of VoterID that participated in both ElectionID 1 and ElectionID2, for improved performance, I'd avoid using an inline view (MySQL calls it a derived table). I'd have the query use a JOIN operation instead. Something like this:

SELECT COUNT(DISTINCT e1.voterID) AS NumVoters
  FROM elections e1
  JOIN elections e2
    ON e2.voterID = e1.voterID
 WHERE e1.electionID = 1
   AND e2.electionID = 2

If you are guaranteed that (voterID, ElectionID) is unique, then the select could be simpler:

SELECT COUNT(1) AS NumVoters
  FROM elections e1
  JOIN elections e2
    ON e2.voterID = e1.voterID
 WHERE e1.electionID = 1
   AND e2.electionID = 2
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

To clarify: Do I need to define a constraint for (VoterID, ElectionID), or do I just need to be sure that they form a unique tuple?
@user3208862: You don't have to define a constraint. I was just pointing out that your query may return results you don't "expect" if there are duplicates. If it's possible that there are duplicates, then the query can be modified slightly, so that the query anticipates possible duplicates and returns "correct" results when duplicates do occur.
You should define them as PRIMARY. Failing that, you should define them as UNIQUE. If they are not UNIQUE, then (aside from very dodgy voting practices), you will require the DISTINCT keyword in your COUNT
0 
SELECT COUNT(*)  
  FROM 
     ( SELECT voterid 
         FROM votes 
        WHERE electionid IN(1,2) 
        GROUP 
           BY voterid 
       HAVING COUNT(*) = 2
     ) x;

This assumes that you have a UNIQUE or PRIMARY KEY formed on (voterid,electionid)

Improve this answer

5 Comments

This will work, even though the first COUNT is in an outer query, and the second COUNT is in a subquery? That seems weird. Apart from that, and apart from being more compact than my query, it looks pretty similar to what I came up with.
I guess I assume that the outer query executes on the subquery, meaning the subquery can stand alone. Your subquery can't stand alone, can it?
Eh? Dunno what you mean.
Your solution works. I can't explain my confusion any better; it's just about my intuition as a novice. Thanks!
The outer query is in a sense redundant, because it simply returns a value equal to the number of rows returned by the subquery, but apart from that, it's fine.
0

I think this should work but I'm not positive... (can't remember if COUNT can be used in a join like this). Let me know?

SELECT COUNT(*)
FROM ELECTIONS e1, ELECTIONS e2
WHERE e1.VoterID = e2.VoterID
    AND e1.ElectionID = 1
    AND e2.ElectionID = 2;
Improve this answer

2 Comments

I'm not sure I understand the theory here. If I join the table to itself, how am I going to get a row that satisfies the WHERE clause?
@user3208862 Think of it as producing all possible pairs of votes by the same user (the join), then restricting it to those pairs where the first vote is in election 1 and the second vote is in election 2. Then count the results.
0

This is as simple as the following 

SELECT voterid, COUNT(DISTINCT electionid) AS electioncount
FROM table
WHERE electionid IN (1, 2) /* substitute elections you are interested in here */
GROUP BY voterid
HAVING electioncount = 2 /* substiture number of election listed in where condition above

The result set size would provide the number of voters that meet your criteria (i.e. there is no reason to aggregate down furter (i.e. like with subselect) to get to that data.

Improve this answer

7 Comments

I think that I understand this, and I think that it will work. I will try it later and, if it works, vote this answer correct. Thanks!
I must have mis-read the question in that case. Oh wait, no I didn't! ;-)
@Strawberry What is there to mis-read about this I want to know the number of voters who voted both in election 1 and in election 2; I don't care about anyone else. This gets him that information exactly, though it doesn't return the count value in the query itself. One simply needs to inspect the result set size to understand how many voters meet this criteria. So this query can fulfill two purposes, it can both tell you the total number (by inspecting the result set size) and allow you to use the result set itself to list out the voters that meet the criteria.
Actually, I will want to use the result set, so this query is useful for that purpose. I didn't quite realize that the query itself doesn't return the count value. But it looks like my original query or a variation works for that. Thanks!
@user3208862 There is no reason whatsoever to do a separate query to get the count. Most any application library you work with to interact with MySQL will expose a method to get a count of the rows in the result set. Thus there should be no need for two separate queries.
|
0

I would recommend something more like this:

SELECT COUNT(*) AS NumVoters
FROM ELECTIONS e1
WHERE e1.ElectionID = 1
AND e1.VoterID in (
    SELECT e2.VoterID
    FROM ELECTIONS e2
    WHERE e2.ElectionID = 2
);

That way you solve the problem, and have only 1 subquery.

Improve this answer

2 Comments

This is less intuitive to me than Mike Brant's solution below, but I will give it a try and vote it up if it works. Thanks!
Thanks for the suggestion. I think that in lines 3 and 4, you meant to write "e1.ElectionID" and "e2.VoterID". With those changes, this works. I think that Spencer7593's suggestion is basically the same as yours, though his suggestion seems a little easier to read.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.