CODEWITHSUNDEEP
sqlpostgresql
mark
mark

Reputation: 873

3 table join with a calculated %

I have the following tables:

POLLS
id | name       | colour
-------------------------
1  | first poll | orange
2  | secon poll | blue
3  | third poll | green

QUESTIONS
id | poll_id | what_to_ask
---------------------------
1  | 1       | How nice is stackoverflow?
2  | 1       | Why do you think that?
3  | 2       | What do you like to eat?

CHOICES
id | question_id | choice_text
------------------------------------
1  | 1           | Very nice
2  | 1           | Moderatley nice
3  | 1           | Evil
4  | 2           | Etc.

Answers
id | choice_id | poll_id | question_id
--------------------------------------
1  | 1         | 1       | 1
2  | 1         | 1       | 1
3  | 2         | 1       | 1
4  | 3         | 1       | 1
5  | 1         | 1       | 1

I'm trying to pull back the following:

My query (below) works well if there is an answer per choice, but if there is only one answer (and perhaps 3 choices), it'll only bring back one row.

How can i bring back each answer for a poll, and then the number of answers and % of answers on each choice.

SELECT Count(a.choice_id) AS answer_count,
     q.what_to_ask,
     c.id,
     c.choice_text,
     COALESCE (Count(a.choice_id) * 100.0 / NULLIF((SELECT Count(*)
                                                    FROM   answers
                                                    WHERE  poll_id = 2), 0), 0
     ) AS percentage
FROM   choices c
RIGHT OUTER JOIN answers a
            ON a.choice_id = c.id
INNER JOIN polls p ON p.id = a.poll_id
INNER JOIN questions q ON c.question_id = q.id
WHERE  p.id = 2
GROUP  BY c.id, q.what_to_ask, c.choice_text

Upvotes: 1

Views: 41

Answers (1)

Pat Jones
Pat Jones

Reputation: 888

I would change the join between choices and answers to a LEFT JOIN:

SELECT Count(a.choice_id) AS answer_count,
     q.what_to_ask,
     c.id,
     c.choice_text,
     COALESCE (Count(a.choice_id) * 100.0 / NULLIF((SELECT Count(*)
                                                    FROM   answers
                                                    WHERE  poll_id = 2), 0), 0
     ) AS percentage
FROM   choices c
LEFT JOIN answers a
            ON a.choice_id = c.id
INNER JOIN polls p ON p.id = a.poll_id
INNER JOIN questions q ON c.question_id = q.id
WHERE  p.id = 2
GROUP  BY c.id, q.what_to_ask, c.choice_text

In making it a RIGHT JOIN, you are telling the query to return rows only when there is a match in the answers table.

Upvotes: 1

Related Questions