The table below is a subset of a table from a db I have access to:

CREATE TABLE trips(trip_id INT, foot BOOLEAN, bike BOOLEAN, bus BOOLEAN, 
  car BOOLEAN, metro BOOLEAN, motorcycle BOOLEAN, train BOOLEAN,
  other BOOLEAN)

-- sample values
INSERT INTO trips (trip_id, foot, bike, bus, car, metro, 
  motorcycle, train, other)
VALUES(19,true,false,false,false,false,false,false,false),
      (20,false,false,false,false,false,false,false,false),
      (56,true,false,true,false,false,false,false,false),
      (65,true,false,false,true,false,false,false,false),
      (77,false,false,false,true,false,false,false,false)

Then for example, I want to produce the following statistics above mode of trip in the table.

• number trips by foot only
• number of trips by bus only
• number of trips by car only etc.., then
• number of trips by foot AND car
• trips by foot AND bus
• total trips for which all modes are FALSE.

I produce this db<>fiddle, but not sure how to filter this stats.

CodePudding user response：

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 

CodePudding user response：

Do logic operation

SELECT * FROM trips WHERE foot = true
AND (bike AND  bus AND  car AND  metro AND 
  motorcycle AND train AND other) = False

SELECT * FROM trips WHERE (foot AND car) = true
AND (bike AND  bus AND  metro AND 
  motorcycle AND train AND other) = False

fiddle