CODEWITHSUNDEEP
sqlpostgresql
Jose Maria Landa
Jose Maria Landa

Reputation: 425

PostgreSQL nested select query

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:

This is what the query returns =(

Upvotes: 1

Views: 160

Answers (2)

fabulaspb
fabulaspb

Reputation: 1263

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

Upvotes: 2

Andomar
Andomar

Reputation: 238296

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

Upvotes: 4

Related Questions