I have a table called discursion that stores all my users' questions:

In addition, I have another table that stores user responses called response_discursion:

All I'm trying to do is bring in all the questions from the discursion table, and add up the total responses for each question from the response_discursion table that is pegged to ID_Discursion.

For that I'm trying the following:

SELECT D.ID_Discursion, 
       D.TEXT_Discursion, 
       Total_Response = 
         (
          SELECT COUNT(RD.ID_ResponseDiscursion) 
          FROM response_discursion AS RD 
          ON RD.ID_Discursion = D.ID_Discursion
         ) 
FROM discursion AS D;

But unfortunately, it doesn't seem to work.

CodePudding user response：

You can join the response table and then group by the discursion id. Use a left join if you want to see discursions with no responses.

select
  d.ID_Discursion,
  d.TEXT_Discursion,
  count(rd.ID_ResponseDiscursion) as Total_Response
from discursion d
left join response_discursion rd ON rd.ID_Discursion = d.ID_Discursion
group by d.ID_Discursion

Note: Normally you need every non-aggregated selected column in the group by. However, there's no need to have text_discursion in the group by because it is unique to each id_discursion.

Demonstration.