PostgreSQL nested select query

Question

Ok. so I have 2 tables: questions and questions_answers

table 1 columns) question_text | question_id
table 2 columns) answer_text   | question_id

I want to perform a query that retrieves each row of questions with a count of the amount of answers that question has... So far I've come up with this:

SELECT *, (select count(*) from questions         
JOIN question_answers on question_answers.question_id =
questions.question_id) as answers from questions

However this query return the total number of answers of all the questions added. So if question 1 has 2 answers and question 2 has 3, this query returns 5 for all rthe questions. I want it to return the amount of answers each questions has. Any help??? Can´t seem to figure it out =(

This is what the query returns:

Andomar · Accepted Answer · 2015-12-30 22:13:38Z

4

select  question_text 
,       count(qa.question_id) as answer_count
from    questions q
left join
        question_answers qa
on      qa.question_id = q.question_id
group by
        q.question_id

answered Dec 30, 2015 at 22:13

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jose Maria Landa Over a year ago

select q.question_id, COUNT(qa.question_id) as answer_count from questions q left join question_answers qa on qa.question_id = q.question_id group by q.question_id (JUST CHANGED IT A BIT AND IT WORKED PERFECTLY!!! THANKS!)

fabulaspb · Accepted Answer · 2015-12-30 22:15:08Z

2

Try this SQL query:

SELECT Q.question_id, COUNT(QA.question_id) as questions_cnt
FROM Questions AS Q
    LEFT JOIN Questions_answers AS QA ON Q.question_id = QA.question_id
GROUP BY Q.question_id

edited Dec 30, 2015 at 22:15

answered Dec 30, 2015 at 22:13

fabulaspb

1,2538 silver badges9 bronze badges

1 Comment

Andomar Over a year ago

count(*) will return 1 for a question with no answers

Collectives™ on Stack Overflow

PostgreSQL nested select query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related