Percentage with condition SQL

user_13757716 0
Viewed 63

Let’s say I have a simple table CITY with ID, NAME, POPULATION columns I wanna know what is the percentage of cities with > 10000 population. I can do it with

select count(*) / (select count(*) from CITY) * 100
from CITY
where population > 10000

or like this

select count(case when population > 100000 then 1 else null end) / count(*) * 100
from CITY

but I was that it's appropriate to use a window function for that. How? When I do something like

select count(*) / sum(count(*)) over () * 100
from CITY
where population > 100000

I get 100 cause over() counts just the remaining rows. So, the question is: how to implement a widow function for counting percentage with condition?

percentage conditional-statements window sql
1 Answers

You don't need window functions. The simplest method uses AVG():

select avg(case when population > 10000 then 100.0 else 0 end) as percentage
from CITY;

Window functions are appropriate when you want to retain the original rows in the data set. When you want to summarize them, then aggregation is what you want -- and in this case, conditional aggregation making use of case expressions.

user_11440350
Related