How to select same column with different conditions

user_12213202 0
Viewed 569

I have a table table1 with these columns:

  • region (varchar)
  • surface (float8)
  • decision (bool)

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 :

  • region
  • the average surface of surfaces where decision is true
  • the average surface of surfaces where decision is false

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.

postgresql
1 Answers

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
user_39842210
Related