Help with SQL query with COUNT in multiple lines

Navigation
0

Hello, I'm doing the following query 

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 it is returning 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 it to return the list of each of the names of each title together with the total count 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 doing 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 all lines. I tried to create count inside a subquery, but because it returned several results, the search was not successful.

    
asked by anonymous 09.12.2018 / 04:37

3 answers

2

The simplest way to do this is with a subquery ; you create a select to return the total of travel 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 at this fiddle that works the way you want.

    
10.12.2018 / 13:32
1

One solution, as indicated in the question, is to calculate the total number of 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
    
10.12.2018 / 11:57
0

Oops. From what I could understand of your doubt, you want the "name" column not to be repeated in your query. So, what you want would be basically to know how many times the client 'x' made the 'y' route, that? In this case, simply add your customer name to your group by. Here's 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 it is not this, I hope others can heal your doubt! Thanks!

    
09.12.2018 / 19:09
Change site style / theme and store it in LocalStorage User field behavior when using the AddTransformed method in sales. (V9.15 and C #)