how to calculate ratio of two counts using sql?

Question

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

This answer seems to help: https://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query select count(*), sum(case when price <= 10 then 1 else 0 end) AS cheapProductCount from toys group by toys — Soccergods, Sep 16 '21 at 20:07

score 2 · Answer 1 · answered Sep 16 '21 at 19:50

2

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.

answered Sep 16 '21 at 19:50

Gordon Linoff

1,242,037
58
646
786

Thanks! what if I have to use counts? – CWeeks Sep 16 '21 at 19:52
@CWeeks . . . I don't understand your comment. Your question is about how to calculate the ratio/percentage. – Gordon Linoff Sep 17 '21 at 02:17

score 1 · Answer 2 · answered Sep 16 '21 at 20:29

1

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

answered Sep 16 '21 at 20:29

Stu

30,392
6
14
33

Thanks this is really helpful! – CWeeks Sep 16 '21 at 21:14

score 0 · Answer 3 · answered Sep 16 '21 at 21:47

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

how to calculate ratio of two counts using sql?

3 Answers3