CODEWITHSUNDEEP
postgresql
Basile
Basile

Reputation: 621

How to select same column with different conditions

I have a table table1 with these columns:

I want to select AVG(surface) for the cases where decision is True and where it is False and group the result by region.

I finally want 3 columns :

I tried :

SELECT 
    region,
    (SELECT AVG(surface_m2) FROM table1 WHERE avis_final_bri),
    (SELECT AVG(surface_m2) FROM table1 WHERE avis_final_bri)
FROM
    table1
GROUP BY 
    region

but the query does not work.

I also tried to define another table the WITH statement but it did not work. I tried with the JOIN but it failed as well.

Upvotes: 2

Views: 1071

Answers (1)

S-Man
S-Man

Reputation: 23666

You can use the FILTER clause:

demo:db<>fiddle

SELECT
    region,
    AVG(surface) FILTER (WHERE decision = true),
    AVG(surface) FILTER (WHERE decision = false)
FROM
    table1
GROUP BY region

Alternatively to use more common SQL, you can use the CASE clause:

SELECT
    region,
    AVG(CASE WHEN decision = true THEN surface END),
    AVG(CASE WHEN decision = false THEN surface END)
FROM
    table1
GROUP BY region

Upvotes: 1

Related Questions