Column splitting error with SQL Subquery

Question

I am using postgresql

I have a query that when I run a division between the columns (soma_neighborhood / Soma_city) it always returns 1, should return the value described in the field and not the count amount of the row, follows example.

I believe I’m wrong here :

(COUNT(distinct neighborhood) / COUNT(distinct tbl.city)) as percentual

How do I make her understand the value of the field and not the account?

SELECT 

city, 
neighborhood, 
count(neighborhood) as Soma_bairros,

(select count(city) FROM tb_listings WHERE city = tbl.city) as Soma_cidade ,

(COUNT(distinct neighborhood) / COUNT(distinct tbl.city)) as percentual

FROM tb_listings tbl

GROUP BY city, neighborhood 

ORDER BY city, neighborhood DESC
Resultado atual:

Percentage returns 100 and not 33 %

You would have to classify field as INT ?

Answer 1

I did not understand what you wanted in the query, is to calculate the percentage of neighborhoods by city, or the total percentage of the table?

I also noticed that the logic of your query is incorrect, because you’re dividing the distinct amount of neighborhoods by the distinct amount of city, as distinct will bring only 1 time each value, then the result will always give 1, because 1/1 = 1.

Due to lack of information about the query objective, I did some tests and managed, with the query below, to calculate the percentage of neighborhoods by city:

SELECT  
CITY, 
NEIGHBORHOOD, 
COUNT(NEIGHBORHOOD) AS SOMA_BAIRROS,
(SELECT COUNT(CITY) FROM TB_LISTINGS WHERE CITY = TBL.CITY) AS SOMA_CIDADE ,
CAST(CAST(COUNT(CITY) AS NUMERIC(4,0)) / CAST(COUNT(NEIGHBORHOOD) OVER (PARTITION BY CITY) AS NUMERIC(4,0)) * 100 AS INT) PERCENTUAL
FROM TB_LISTINGS TBL
GROUP BY CITY, NEIGHBORHOOD
ORDER BY CITY, NEIGHBORHOOD DESC;

Note: the cast will serve to format the number in integer, not to get the broken value in the result of the calculation, if you do not want, can follow the form below:

SELECT  
CITY, 
NEIGHBORHOOD, 
COUNT(NEIGHBORHOOD) AS SOMA_BAIRROS,
(SELECT COUNT(CITY) FROM TB_LISTINGS WHERE CITY = TBL.CITY) AS SOMA_CIDADE ,
(COUNT(CITY) / SUM(COUNT(NEIGHBORHOOD)) OVER (PARTITION BY CITY)  * 100) PERCENTUAL 
FROM TB_LISTINGS TBL
GROUP BY CITY, NEIGHBORHOOD 
ORDER BY CITY, NEIGHBORHOOD DESC;

The following is an explanation of how the over Partition by https://www.devmedia.com.br/funcoes-analiticas-em-oracle/3707

Good studies!