Accounts how many equal ids and make a larger result table for the smaller

Question

I’m doing an exercise that asked:

Create a table postagem where will have id, titulo_postagem, and also create a table called comentarios where will have id, id_postagem, comentario.

After the created tables it is asked to return the posts with more comments.

I was running a query as follows, but to no avail:

$selPostMaisComentados = $conn->prepare("SELECT * FROM postagem AS p INNER JOIN comentarios AS c ON c.id_postagem = p.id");
$selPostMaisComentados->execute();

Table postagem:

id   titulo
1    Poste aqui...
2    Etc...
3    fala mano

Table comentarios:

id id_postagem  comentario
1  1            oi
2  1            fala ae
3  2            iae
4  1            to inventando os comentario......
5  2            huhu
6  3            aiaiai

Answer 1

You can make a subselect to count the number of comments per post and then sort in descending order

  SELECT p.id
        ,p.titulo_postagem
        ,a.total
    FROM postagem AS p 
    JOIN ( SELECT COUNT(c.*) total, c.id_postagem
             FROM comentarios AS c 
            WHERE c.id_postagem = p.id
            GROUP BY c.id_postagem
          ) a
    WHERE a.id_postagem = p.id
     ORDER BY a.total  DESC   ;
          

PS: I did not test the code