SQL calculate percentage using table self join

Question

So I have this table:

The columns mean house type, quantity and RegionName. I need to build a query which returns the percentage of a certain house type in each region, like this:

nomRegion    tipo                   Prct
Central      Casa independiente     23.5
brunca       Casa independiente     54.7
chorotega    Casa independiente     12.4
....         Casa independiente     ....

I need to join the table with itself to get the amount of houses of my required type per Region and then divide it by the total amount of houses in each Region. I tried writing this query but it returns 0 in all the percentage fields.

select T.nomRegion, T.Tipo, (T.Cantidad/SUM(V.Cantidad))*100
from dbo.TipoVivienda T join dbo.TipoVivienda V on T.nomRegion = V.nomRegion
where T.Tipo = 'Casa independiente'
group by T.nomRegion, T.Tipo, T.Cantidad

I have tried other variations, basing myself on this question: SQL issue: Calculating percentages and using multiple joins to the same table

select T.nomRegion, T.Tipo, 100 * (T.Cantidad/V.Cantidad)
from dbo.TipoVivienda T inner join (select nomRegion as nomRegion, SUM(Cantidad) as Cantidad
                                    from dbo.TipoVivienda
                                    group by nomRegion) V on T.nomRegion = V.nomRegion
where T.Tipo = 'Casa independiente'
group by T.NomRegion, T.Tipo, T.Cantidad, V.Cantidad

But the query still returns 0 as a percentage for all regions. Not sure what I'm doing wrong here... I tried another variation of the query using correlated queries but the result is the same:

select T.nomRegion, T.Tipo, (T.Cantidad / (select SUM(V.cantidad)
                                           from dbo.TipoVivienda V
                                           where V.nomRegion = T.nomRegion
                                           group by V.nomRegion))*100
from dbo.TipoVivienda T
where T.tipo = 'Casa independiente'

Any ideas?

Answer 1

Multiply with 1.0 to get correct results as sql server does integer arithmetic.

(T.Cantidad*1.0/SUM(V.Cantidad))*100 

or multiply by 100 first and then do division.

T.Cantidad*100.0/SUM(V.Cantidad)