Group two tables

Navigation
0

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

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

The Evento table contains several events for each Pessoa .

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

| Pessoa | Email           | Quantidade Eventos |
| nome1  | [email protected] | 5                  |
| nome2  | [email protected] | 10                 |
    
asked by anonymous 15.08.2016 / 14:32

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
    
15.08.2016 / 14:41
1

The correlated subquery is executed once for each row of the result which can perform very poorly. Instead, merge the tables: 

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
    
15.08.2016 / 15:44
MVVM and Binding for different classes Call View on Another ASP.NET MVC Controller