Help with SQL query with COUNT in multiple rows

Question

Hello, I’m doing the following consultation

SELECT p.id, c.nome, v.titulo, count(v.titulo)
FROM passageiros   AS p
LEFT JOIN clientes AS c ON c.id = p.id_cliente
LEFT JOIN viagens  AS v ON v.id = p.id_viagem
GROUP BY v.titulo;

And she’s returning me the following result:

id| nome       | titulo                  |  count(v.titulo)
1 | 'bruno'    | 'gramado'               |  5
6 | 'alea vang'| 'gramado - mundo magico'|  5
11| 'bruno'    | 'beto carrero'          |  3
14| 'bruno'    | 'caminhos rurais'       |  6

But what I would like is for her to return the list of each of the names of each title along with the Count total of that title:

id| nome               | titulo                   | count(v.titulo)
1 | 'bruno'            | 'gramado'                | 5
2 | 'tamires'          | 'gramado'                | 5
3 | 'christen wiggins' | 'gramado'                | 5
4 | 'justine howard'   | 'gramado'                | 5
5 | 'anika hammond'    | 'gramado'                | 5
6 | 'alea vang'        | 'gramado - mundo magico' | 5
7 | 'fredericka jensen'| 'gramado - mundo magico' | 5
8 | 'alexa duncan'     | 'gramado - mundo magico' | 5
9 | 'lynn mclean'      | 'gramado - mundo magico' | 5
10| 'allegra cantrell' | 'gramado - mundo magico' | 5
11| 'bruno'            | 'beto carrero'           | 3 
12| 'tamires'          | 'beto carrero'           | 3
13| 'christen wiggins' | 'beto carrero'           | 3
14| 'bruno'            | 'caminhos rurais'        | 6
15| 'tamires'          | 'caminhos rurais'        | 6
16| 'christen wiggins' | 'caminhos rurais'        | 6
17| 'alexa duncan'     | 'caminhos rurais'        | 6
18| 'lynn mclean'      | 'caminhos rurais'        | 6
19| 'allegra cantrell' | 'caminhos rurais'        | 6

EDIT: When making the query using GROUP BY v.titulo, p.id; i have the return of the complete list of names, however the count returns only 1 on every line. I tried to create the count within a subquery, but as it returns multiple results, the search was unsuccessful.

Answer 1

The simplest way to do this is with a subconsultation; you create a select to return the total trip records of the searched line:

SELECT p.id, c.nome, v.titulo, (select count(*) from passageiros p2 where p2.id_viagem = p.id_viagem) as total
FROM passageiros AS p
LEFT JOIN clientes AS c ON c.id = p.id_cliente
LEFT JOIN viagens AS v ON v.id = p.id_viagem

Take a look in that fiddle that is working as you wish.

Answer 2

One solution, as indicated in the question, is to calculate the total passengers for each trip in a subquery. Here is an example that should return the expected result.

SELECT p.id, c.nome, v.titulo, t.num_total_passageiros
  FROM passageiros p
  LEFT JOIN clientes c 
    ON c.id = p.id_cliente
  LEFT JOIN viagens v
    ON v.id = p.id_viagem
  LEFT JOIN 
 (  
    SELECT ip.id_viagem, COUNT(DISTINCT ip.id) as num_total_passageiros
      FROM viagens iv
     GROUP BY ip.id_viagem     
 ) t
   ON t.id_viagem = v.id
ORDER BY p.id

Answer 3

Oops. From what I understand of your doubt, you want the "name" column not to repeat in your query. So what you want is to basically know how many times the X client made the y trip, is that it? In this case, simply add to your group by the client name. Below is an example:

SELECT p.id, c.nome, v.titulo, count(v.titulo)
FROM passageiros   AS p
LEFT JOIN clientes AS c ON c.id = p.id_cliente
LEFT JOIN viagens  AS v ON v.id = p.id_viagem
GROUP BY c.nome, v.titulo;

I hope I have helped! If this is not the case, I hope others can resolve your doubt! Thanks!