firing a query based on the if statement in Postgres

Question

I would like to perform a query based on the condition from a variable. but I can't use case structure because the column I want to be included in the query is in a "GROUP BY" statement.

    SELECT COUNT (rs.id), 
        c.title "City", 
        date '{{ Initial date }}' + interval '1 month' AS "Date"
FROM rooms_reservations rs
    LEFT JOIN lead_lead ll ON rs.journey_id = ll.id
    LEFT JOIN cities c ON ll.city_id = c.id
  WHERE rs.start_date <= date '{{ Initial date }}'+ interval '1 month'
    AND c.title = '{{ City }}'
GROUP BY "Date",
         c.title -- <- the column appear in a group by statement

{{ City }} is a redash variable

I need to the results to be shown for all cities, so it can be queried by this query

    SELECT COUNT (rs.id), 
        --c.title "City", 
        date '{{ Initial date }}' + interval '1 month' AS "Date"
FROM rooms_reservations rs
    LEFT JOIN lead_lead ll ON rs.journey_id = ll.id
    LEFT JOIN cities c ON ll.city_id = c.id
  WHERE rs.start_date <= date '{{ Initial date }}'+ interval '1 month'
    --AND c.title = '{{ City }}' -- <- ex. NYC, WAS, BOS, etc.
GROUP BY "Date" --,
         --c.title

So I would like to have some thing like this: if condition = 'all_cities' then execute 2nd query if condition != 'all_cities' then execute 1st query.

Is it possible? I use read only replica of postgres db

Serg · Accepted Answer · 2022-01-26 17:09:08Z

1

You can use a CASE expression. Replace <condition> with a real one

SELECT COUNT (rs.id), 
        CASE WHEN <condition> THEN c.title ELSE 'ALL' END "City", 
        date '{{ Initial date }}' + interval '1 month' AS "Date"
FROM rooms_reservations rs
LEFT JOIN lead_lead ll ON rs.journey_id = ll.id
LEFT JOIN cities c ON ll.city_id = c.id
WHERE rs.start_date <= date '{{ Initial date }}'+ interval '1 month'
    AND c.title = '{{ City }}'
GROUP BY "Date",
     CASE WHEN <condition> THEN c.title ELSE 'ALL' END

Demo

answered Jan 26, 2022 at 17:09

Serg

22.8k5 gold badges23 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

firing a query based on the if statement in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related