PostgreSQL HAVING clause

Question

I posted on here last night looking for help with some homework. I'm down to my last question.

Here's the relevant piece of the schema I am working with:

CREATE TABLE votesOnPoll(
  user_id int,
  poll_id int,
  option_id int, 
  voteDate date,
  CONSTRAINT votesOnPoll_pk PRIMARY KEY (user_id, poll_id),
  CONSTRAINT votesOnPoll_user_fk FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT votesOnPoll_poll_fk FOREIGN KEY (poll_id,option_id) REFERENCES pollOptions(poll_id, option_id)
);

I am trying to write a query that will return the option_id of poll option with the most votes, as well as that number of votes. This is only on poll 10.

I can successfully return the number of votes for each option with the following query:

SELECT p10.oid AS option_id, MAX(p10.votecount)
FROM (SELECT option_id AS oid, COUNT(DISTINCT user_id) AS votecount
      FROM votesOnPoll
      WHERE poll_id = 10
      GROUP BY option_id) AS p10
GROUP BY p10.oid;

which returns, in this case, the following relation:

option_id | max
-----------+-----
         0 |   7
         1 |  10
         2 |  11

I would like to trim this down so it only has the tuple (2, 11) (or whatever the winning option is). I thought I would be able to accomplish this by adding the following HAVING clause:

HAVING COUNT(p10.votecount) >= ALL (SELECT COUNT(DISTINCT user_id)
                                    FROM votesOnPoll
                                    WHERE poll_id = 10
                                    GROUP BY option_id)

However, this instead returns me an empty relation.

I have confirmed that the inner SELECT statement here returns what I expect - that is, it returns a relation with a single attribute, which is the number of votes on a particular option. In this case, that return value is

 count
-------
     7
    10
    11

Any ideas?

Thank you very much for your time.

Answer 1

Aaaaaaand about 5 seconds after posting this, I found the error. Instead of saying "HAVING COUNT(...)", I used "HAVING MAX(...)", and I am getting the correct information.