CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionshaving
Ganon11
Ganon11

Reputation: 151

PostgreSQL HAVING clause

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 the 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, in this case, returns 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 returns an empty relation instead.

I have confirmed that the inner SELECT statement 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?

Upvotes: 3

Views: 1980

Answers (1)

Ganon11
Ganon11

Reputation: 151

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.

Upvotes: 2

Related Questions