SQL Count operation

Question

I have 2 queries using same table: total, I need to evaluate the percentage of the result. (Postgres 9.4)

SELECT COUNT(*) FROM total
SELECT COUNT(*) FROM total WHERE field1 = False

Example:

• Total count no filter returns 100 rows
• Total with field1 = False returns 10 rows.

I need to get the 10%, meaning the percentage meeting specific condition. 100/10 What is the best way to calculate it this condition in SQL?

SELECT * FROM (
SELECT COUNT(*) FROM total /
SELECT COUNT(*) FROM total WHERE field1 = False ) 

Answer 1

I would do this as:

select avg(case when field = false then 100.0 else 0 end) as percentage
from total;

Assume field is not NULL, you can also do:

select avg( (not field)::int ) * 100 as percentage
from total;

Answer 2

SELECT COUNT(CASE 
               WHEN field1=false THEN 1 
             END) / COUNT(*)*100 AS percentage 
FROM table

You can use conditional aggregation using case expression

Answer 3

If field1 column is BOOLEN Type,There is another cool way

you can use SUM function like this.

SELECT SUM((NOT field1)::INT) * 100/SUM(1) percentage 
FROM total

sqlfiddle:http://sqlfiddle.com/#!15/eb9fd1/21