SQL: Record counter with data grouping of different fields

Question

SQL: Record counter with data grouping of different fields

Navigation

#1 by (0 votes)

1

I'm having a hard time building a SQL. I have a address table with the street and crossover fields. I need to count the numbers of records that have the same crossover and group them together Example:

id | rua | Cruzamento
1  | Rua Brasil | Rua Parana 
2  | Rua Parana | Rua Brasil
3  | Rua Brasil |
4  | Rua Parana |
5  | Rua Brasil | Rua Parana
6  | Rua Parana | Rua São Paulo

The query should return:

cont | descricao
3 | Rua Brasil - Rua Parana
1 | Rua Parana - Rua São Paulo

Can anyone give a light? Thank you.

sql postgresql hibernate

asked by anonymous 12.12.2016 / 03:21

1 answer

Offline Map on Android intellisense - Visual Studio

score 0 · Accepted Answer

According to the question: "I need to count the numbers of records that have the same crossover and group it"

Then we would have:

SELECT DISTINCT COUNT(id), rua || ' - ' || cruzamento As descricao_Rua_Cruzamento
FROM endereco 
WHERE rua ilike '%' || rua || '%' 
AND cruzamento ilike '%' || cruzamento || '%'  
GROUP BY rua,cruzamento
ORDER BY COUNT(id) DESC ;

# count | descricao_rua_cruzamento
# 2 | Rua Brasil - Rua Parana
# 1 | Rua Parana - Rua Brasil
# 1 | Rua Parana - Rua São Paulo

So I'm counting records that have the same crossover. According to your question. Since Rua Brasil crosses with Parana Street twice, already the other Street, Parana Street that crosses with Brazil Street only once in the database. Now if it is "the SQL script recognizes that Rua Brasil Parana Street and Parana Street Brazil Street is the same crossroads" It would be another question, right? It is necessary to modify the script above to answer the second question.