PostgreSQL: count occurrence of true values in columns

Question

I have the following table:

CREATE TABLE trips(
     trip_id int,
     true_foot boolean,
     true_bike boolean,
     true_bus boolean,
     true_car boolean,
     true_metro boolean
)

INSERT INTO trips (trip_id,true_foot,true_bike,true_bus,true_car,true_metro) 
VALUES 
  (563097,'t','f','f','f','f'),
  (596303,'f','f','f','t','f'),
  (595648,'f','f','f','t','f'),
  (566061,'t','f','f','f','f'),
  (566753,'t','f','f','f','f'),
  (561179,'t','f','f','f','f'),
  (535519,'f','f','f','f','f'),
  (548460,'t','f','f','f','f'),
  (543477,'f','f','f','t','f'),
  (540797,'t','f','f','f','f')

Only one column has true value in a row (or none). Then I want to count all true values for foot, bike, bus, etc...

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

ERROR:  syntax error at or near "'walk'"
LINE 3:  COUNT(*) FILTER (WHERE true_foot IS TRUE ) AS 'walk',

Ah, thank you. SQL error log is often doesn't give precise hint. — arilwan
– arilwan, Commented Jun 25, 2020 at 11:45

GMB · Accepted Answer · 2020-06-25 12:11:43Z

3

I like to use SUM() for this. You just need to convert the booleans to integer values (true becomes 1, false becomes 0).

SELECT 
    SUM( (true_food)::int )  AS walk,
    SUM( (true_bike)::int )  AS bike,
    SUM( (true_bus)::int )   AS bus,
    SUM( (true_car)::int )   AS car,
    SUM( (true_metro)::int ) AS metro
FROM trips

answered Jun 25, 2020 at 12:11

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-06-25 11:45:30Z

1

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;

answered Jun 25, 2020 at 11:45

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

3 Comments

arilwan Over a year ago

Thank you so much, will definitely remove the IS TRUE

arilwan Over a year ago

Ah, my bad, I was considering ease of readability, (not aware of performance).

arilwan Over a year ago

Sure EXPLAIN shows that this is nearly 3x faster, thanks for the information.

Collectives™ on Stack Overflow

PostgreSQL: count occurrence of true values in columns

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related