I have below properties table;

properties table ----> table

I am trying to write a query to pick % of house listed vs % of units listed, broken down by suburb. Rounding of % to be done. Result should be looking something like

I have tried many code alterations but could not get it done. If anybody can help, that would be much appreciated.

Thanks in advance

CodePudding user response：

You have to find the total of houses and units in each suburb.

select count(\*) as total 
from Table1
group by suburb;

And then use the result as a subquery when finding the number of houses or units in each suburb.

This may get you some of the way:

select suburb, type, count(\*)/
(select count(\*) as total 
 from Table1 as T1 
 where Table1.suburb = T1.suburb 
 group by suburb)
as "%"
from Table1 
group by type, suburb;

CodePudding user response：

I figured it out.

SELECT suburb,
ROUND((100xCOUNT(CASE WHEN type="house" THEN 1 ELSE NULL END))/count(type)) AS houses_percent,
ROUND((100xCOUNT(CASE WHEN type="unit" THEN 1 ELSE NULL END))/count(type)) AS units_percent
FROM properties
GROUP BY suburb
ORDER BY suburb

Thanks for help guys