MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create  table test_table(PLACE varchar(100), TYPE varchar(100), METER Int, PRICE Int);
insert  into test_table values
    ('Place_A', 'flat', 1, 10),
    ('Place_A', 'flat', 2, 11),
    ('Place_A', 'house', 3, 12),
    ('Place_A', 'house', 4, 13),
    ('Place_B', 'flat', 5, 14),
    ('Place_B', 'flat', 6, 15),
    ('Place_B', 'flat', 7, 16),
    ('Place_B', 'house', 8, 17),
    ('Place_B', 'house', 9, 18),
    ('Place_B', 'house', 10, 19);
WITH AGG AS (
    Select PLACE, TYPE, 
    AVG(METER) AS `AVG(METER)`
    , MAX(PRICE) AS `MAX(PRICE)`
    , COUNT(PRICE) 
    From test_table 
    Group By PLACE, TYPE) 
SELECT (Select `AVG(METER)` FROM AGG Where PLACE = O.PLACE AND TYPE = 'flat')
FROM test_table as O


(Select `AVG(METER)` FROM AGG Where PLACE = O.PLACE AND TYPE = 'flat')


1.5000

1.5000

1.5000

1.5000

6.0000

6.0000

6.0000

6.0000

6.0000

6.0000


With AGG As (
    Select PLACE, TYPE, 
    AVG(METER) AS "AVG(METER)"
    , MAX(PRICE) AS "MAX(PRICE)"
    , COUNT(PRICE) 
    From test_table 
    Group By PLACE, TYPE
)
Select 
    PLACE, 
    (Select `AVG(METER)` FROM AGG Where PLACE = O.PLACE AND TYPE = 'flat') AS "FLAT_AVG",
    (Select `MAX(PRICE)` FROM AGG Where PLACE = O.PLACE AND TYPE = 'flat') AS "FLAT_MAX",
    (Select `AVG(METER)` FROM AGG Where PLACE = O.PLACE AND TYPE = 'house') AS "HOUSE_AVG",
    (Select `MAX(PRICE)` FROM AGG Where PLACE = O.PLACE AND TYPE = 'house') AS "HOUSE_MAX"
  FROM test_table as O
  Group By PLACE


PLACE
FLAT_AVG
FLAT_MAX
HOUSE_AVG
HOUSE_MAX


Place_A
1.5000
11
3.5000
13

Place_B
6.0000
16
9.0000
19


With AGG As (
    Select PLACE
         ,TYPE
         ,AVG(METER) AS "AVG(METER)"
         ,MAX(PRICE) AS "MAX(PRICE)"
         ,COUNT(PRICE)
    From test_table 
    Group By PLACE, TYPE
)
Select 
    O.PLACE
    ,MAX(IF(A.TYPE='flat', `AVG(METER)`, NULL)) AS FLAT_AVG
    ,MAX(IF(A.TYPE='flat', `MAX(PRICE)`, NULL)) AS FLAT_MAX
    ,MAX(IF(A.TYPE='house', `AVG(METER)`, NULL)) AS HOUSE_AVG
    ,MAX(IF(A.TYPE='house', `MAX(PRICE)`, NULL)) AS HOUSE_MAX
FROM test_table as O
JOIN AGG A
  ON A.PLACE = O.PLACE
GROUP BY O.PLACE;


PLACE
FLAT_AVG
FLAT_MAX
HOUSE_AVG
HOUSE_MAX


Place_A
1.5000
11
3.5000
13

Place_B
6.0000
16
9.0000
19