How to filter a conditional statement with a conditional condition in postgresql

Question

The idea is to filter the classes_offered that are equal to 1

    SELECT
        to_char("startDate", 'HH24:MI') as Hour,
        dow as "Weekdays", 
        fitness_experience as Class_Type,
        name as Training_Ground,
        oic_name as OIC,
        count(*) AS total_bookings,
        count(distinct "startDate") AS classes_offered,
        (count(*)::numeric / count(distinct "startDate")::numeric) AS avg_bookings
    FROM (
        SELECT start_date as "startDate", 
               name, oic_name, dow, fitness_experience
        FROM clean.dim_bookings
        WHERE 1=1 
          and status = 'ACTIVE' 
          and fitness_experience = 'OPEN' 
         [[AND venue_name={{Units}}]] 
         [[AND name={{training_ground}}]] 
         [[AND start_date >= {{from_date}}]] 
         [[AND start_date <= {{to_date}}]] 
         [[AND oic_name={{OIC}}]] 
         [[AND {{workout_category}}]]
       ) t
    GROUP BY
        1, 2, 3, name, oic_name
    ORDER BY
        1, 2, 3, name, oic_name

Answer 1

You're almost there. Add a HAVING clause to filter by an aggregation result.

SELECT
    to_char("startDate", 'HH24:MI') as Hour,
    dow as "Weekdays", 
    fitness_experience as Class_Type,
    name as Training_Ground,
    oic_name as OIC,
    count(*) AS total_bookings,
    count(distinct "startDate") AS classes_offered,
    (count(*)::numeric / count(distinct "startDate")::numeric) AS avg_bookings
FROM (
    SELECT start_date as "startDate", name, oic_name, dow, fitness_experience
    FROM clean.dim_bookings
    WHERE 1=1 
    and status = 'ACTIVE' 
    and fitness_experience = 'OPEN' [[AND venue_name={{Units}}]] [[AND name={{training_ground}}]] [[AND start_date >= {{from_date}}]] [[AND start_date <= {{to_date}}]] [[AND oic_name={{OIC}}]] [[AND {{workout_category}}]]) t
GROUP BY
    1, 2, 3, name, oic_name
HAVING
    count(distinct "startDate") = 1    
ORDER BY
    1, 2, 3, name, oic_name