Group two tables

Asked

Viewed 68 times

0

I have two tables. A call Pessoa and another call Evento.

Pessoa owns the fields id, name and email. Evento owns the fields id and pessoa_id.

The table Evento contains several events for each Pessoa.

I would like to group all the people, and the amount of events related to it. The new table would be:

| Pessoa | Email           | Quantidade Eventos |
| nome1  | [email protected] | 5                  |
| nome2  | [email protected] | 10                 |
postgresql

2 answers

1


One option is to use a subselect:

SELECT p.nome, p.email,
       (SELECT COUNT(*) FROM eventos e
        WHERE e.pessoa_id = p.pessoa_id) AS qtde_eventos
FROM pessoas p
ORDER BY p.nome

1

The correlated subconsultation is executed once for each line of the result which can have a very bad performance. Instead make the tables join:

select p.name, p.email, count(*) as eventos
from
    pessoa p
    left join
    evento e on p.id = e.pessoa_id
group by p.id, p.name, p.email
order by p.name
;
 name  |      email      | eventos 
-------+-----------------+---------
 nome1 | [email protected] |       5
 nome2 | [email protected] |      10

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.