SQL: Record counter with grouping of data from different fields

Question

I’m having a hard time building an SQL. have a table addressee with the strings fields street and crossing. I need to count the numbers of records that have the same crossing and group them 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 someone shed some light? Thank you.

Answer 1

According to the question: "I need to count the numbers of records that have the same crossing 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 the records that have the same crossing. According to your question. Since Rua Brasil crosses Rua Parana twice, the other Rua Parana crosses Rua Brasil once in the database. Now if it is "the SQL script recognizes that Rua Brasil Rua Parana and Rua Parana Rua Brasil is the same intersection" That would be another question, wouldn’t it? Modifications to the above script are necessary to address the second question.