Postgresql - SQL - Count of 'True' Values

postgresql - sql - count of `true` values

SELECT COALESCE(sum(CASE WHEN myCol THEN 1 ELSE 0 END),0) FROM <table name>

or, as you found out for yourself:

SELECT count(CASE WHEN myCol THEN 1 END) FROM <table name>

PostgreSQL: count occurrence of true values in columns

Single quotes should only be used for string and date constants. You don't need them, so remove them.

Also, is true is actually redundant. So:

SELECT COUNT(*) FILTER (WHERE true_foot) AS walk,
       COUNT(*) FILTER (WHERE true_bike) AS bike,
       COUNT(*) FILTER (WHERE true_bus) AS bus,
       COUNT(*) FILTER (WHERE true_car) AS car,
       COUNT(*) FILTER (WHERE true_metro) AS metro
FROM trips;
 

SQL counting unique true values on join table

If you are interested in the number of users that have at least 1 row with (style IS TRUE) in access_rights, aggregate access_rights before you join:

SELECT count(style OR NULL) AS style_ct
FROM   users
JOIN  (
   SELECT user_id, bool_or(style) AS style
   FROM   access_rights
   GROUP  BY 1
   ) u USING (user_id);

Using JOIN, since users without any entries in access_rights don't count in this case.

Using the aggregate function bool_or().

Or even simpler:

SELECT count(*) AS style_ct
FROM   (
   SELECT user_id
   FROM   access_rights
   GROUP  BY 1
   HAVING bool_or(style)
   );

This is assuming a foreign key enforcing referential integrity, so there is no access_rights.user_id without a corresponding row in users.

Also assuming no NULL values in access_rights.user_id, which would increase the count by 1 - and can be countered by using count(user_id) instead of count(*).

Or (if that assumption is not true) use an EXISTS semi-join:

SELECT count( EXISTS (
              SELECT  1
              FROM    access_rights
              WHERE   user_id = u.user_id
              AND     style  -- boolean value evaluates on its own
              ) OR NULL
            )
FROM   users u;

I am using the capabilities of true boolean values to simplify the count and the WHERE clause. Details:

Compute percents from SUM() in the same SELECT sql query

PostgreSQL count(boolean expression)

Use count(condition or null)

select
    count(field1 = 1 or null) as filed1_ok,
    count(field1 = 0 or null) as filed1_bad,
    extract(MONTH from rpt_date) AS mth
where rpt_date between frdate and todate
group by mth

true or null evaluates to true. false or null evaluates to null. Since count does not count nulls that is exactly what you want.

In other SQL dialects and also in Postgresql it can be done using case

select
    coalesce(sum(case field1 when 1 then 1 end), 0) as filed1_ok,
    coalesce(sum(case field1 when 0 then 1 end), 0) as filed1_bad,
    extract(MONTH from rpt_date) AS mth
where rpt_date between frdate and todate
group by mth

I consider it verbose and opaque compared to the count(condition or null) Postgresql option.

Calculating a percentage with SQL based on count of boolean fields

You can use AVG() to calculate the ratio:

SELECT p.foreign_key_id, count(*) as total_responses,
       avg( (p.was_useful)::int ) as useful_ratio
FROM table_name p
GROUP BY p.foreign_key_id;

I should note that your method -- although much more verbose -- should work. The problem is that Postgres does integer division. So, if you change the then 1 to then 1.0 it will produce a non-integer value.

PostgreSQL how to use count with boolean true value only

If I understand correctly, you can use avg():

select avg(p1.patient_health::int)

Statistics of TRUE/FALSE rows from a multi-BOOLEAN columns TABLE

You can construct your queries following the template below

Foot only

SELECT count(*) nf 
FROM trips 
WHERE foot 
   AND NOT (bike OR bus OR car OR metro OR motorcycle OR train OR other)

Foot + car only

SELECT count(*) nfc
FROM trips 
WHERE foot AND car
AND NOT (bike OR bus OR metro OR motorcycle OR train OR other)

The same in a single query using a conditional aggregation

SELECT 
  count(*) filter(where foot 
                       AND NOT (bike OR bus OR car OR metro OR motorcycle OR train OR other)) nf,
  count(*) filter(where foot AND car
                       AND NOT (bike OR bus OR metro OR motorcycle OR train OR other)) nfc 
FROM trips 

---

Related Topics