CODEWITHSUNDEEP
sqlpostgresql
CWeeks
CWeeks

Reputation: 427

how to calculate ratio of two counts using sql?

I have a table (shown below) where I need to calculate percent of toys that have price lower than 10. How do I do it in sql?

toy price
a   9
b   12
c   15
d   5

Upvotes: 0

Views: 1172

Answers (3)

S.Roeper
S.Roeper

Reputation: 359

This can also be written without case which might be useful if you need more categories

select sum(c1), sum(c2), sum(c1)*100/sum(c2) as 'ratio' from
(
  select count(*) as c1, 0 as c2 from t where price < 10
  union all
  select 0 as c1, count(*) as c2 from t c1
) counts

Upvotes: 0

Stu
Stu

Reputation: 32599

To simply get the ratio as a percentage you can just do

select Sum(case when price<10 then 1.0 end)/Count(*) * 100 as PercentLessThanTen
from t

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

A simple method uses avg():

select avg( (price < 10)::int ) as ratio
from toys;

If you want a percentage between 0 and 100, then multiply by 100.

Upvotes: 2

Related Questions