Query to return specific values

Question

Given a table with:
-property_id(integer)
-distance in metres(integer)
-corresponding place type(text)

I have to write a query to return the following:
1. property_id
2. Closest hospital
3. Closest restaurant or cafe
4. Number of restaurants or cafes within 3km
5. Number of restaurants or cafes within 5km

create table property_poi_distances(
    property_id integer,
    place_type text,
    distance integer
);

insert into property_poi_distances(property_id,place_type,distance)
    values
        (1,'Hospital',100),
        (1,'Hospital',200),
        (1,'Restaurant',1000),
        (1,'Restaurant',2500),
        (1,'Cafe',2000),
        (2,'Hospital',5000),
        (2,'Restaurant',2500),
        (2,'Restaurant',4000),
        (2,'Cafe',1000),
        (3,'Hospital',10000),
        (3,'Restaurant',9000);


select t1.property_id,
    min(t2.distance) as closest_hospital,
    min(t3.distance) as closest_restaurant_cafe,
    count(t4.property_id) as number_restaurants_3km,
    count(t5.property_id) as number_restaurants_5km
from property_poi_distances as t1
         join
     property_poi_distances as t2 on t1.property_id = t2.property_id
         join
     property_poi_distances as t3 on t1.property_id = t3.property_id
         join
     property_poi_distances as t4 on t1.property_id = t4.property_id
         join
     property_poi_distances as t5 on t1.property_id = t5.property_id

where
    t2.place_type = 'Hospital'
    and (t3.place_type = 'Restaurant' or t3.place_type = 'Cafe')
    and ((t4.place_type = 'Restaurant' or t4.place_type = 'Cafe') and t4.distance<=3000)
    and ((t4.place_type = 'Restaurant' or t4.place_type = 'Cafe') and t4.distance<=5000)

group by t1.property_id;

Expected output:

Answer 1

You can do it with conditional aggregation:

select property_id,
    min(case when place_type  = 'Hospital' then distance end) as closest_hospital,
    min(case when place_type in ('Restaurant', 'Cafe') then distance end) as closest_restaurant_cafe,
    sum(place_type in ('Restaurant', 'Cafe') and distance <= 3000) as number_restaurants_3km,
    sum(place_type in ('Restaurant', 'Cafe') and distance <= 5000) as number_restaurants_5km
from property_poi_distances
group by property_id;

See the demo.
Results

| property_id | closest_hospital | closest_restaurant_cafe   | number_restaurants_3km | number_restaurants_5km |
| ----------- | ---------------- | ------------------------- | ---------------------- | ---------------------- | 
| 1           | 100              | 1000                      | 3                      | 3                      |
| 2           | 5000             | 1000                      | 2                      | 3                      |
| 3           | 10000            | 9000                      | 0                      | 0                      |

Answer 2

One solution:

SELECT
  tblPIDs.property_id,
  tblH.closest_hospital,
  tblR.closest_eatery,
  IFNULL(tblR3km.number_eateries_3km, 0) AS number_eateries_3km,
  IFNULL(tblR5km.number_eateries_5km, 0) AS number_eateries_5km
FROM
  (SELECT DISTINCT(property_id)
   FROM property_poi_distances) tblPIDs
INNER JOIN
  (SELECT property_id, MIN(distance) AS closest_hospital
   FROM property_poi_distances
   WHERE place_type = 'Hospital'
   GROUP BY property_id) tblH ON (tblPIDs.property_id = tblH.property_id)
INNER JOIN
  (SELECT property_id, MIN(distance) AS closest_eatery
   FROM property_poi_distances
   WHERE place_type IN ('Restaurant', 'Cafe')
   GROUP BY property_id) tblR ON (tblPIDs.property_id = tblR.property_id)
LEFT OUTER JOIN
  (SELECT property_id, COUNT(*) AS number_eateries_3km
   FROM property_poi_distances
   WHERE place_type IN ('Restaurant', 'Cafe')
   AND distance <= 3000
   GROUP BY property_id) tblR3km ON (tblPIDs.property_id = tblR3km.property_id)
LEFT OUTER JOIN
  (SELECT property_id, COUNT(*) AS number_eateries_5km
   FROM property_poi_distances
   WHERE place_type IN ('Restaurant', 'Cafe')
   AND distance <= 5000
   GROUP BY property_id) tblR5km ON (tblPIDs.property_id = tblR5km.property_id)
;

Answer 3

To keep thing simple you can use subquery.

select 
    t.property_id,
    (select min(t1.distance) from property_poi_distances as t1 where t1.place_type="Hospital" and t1.property_id=t.property_id) as closest_hospital,
    (select min(t1.distance) from property_poi_distances as t1 where t1.place_type IN ("Restaurant","Cafe") and t1.property_id=t.property_id) as closest_restaurant_cafe,
    (select count(*) from property_poi_distances as t1 where t1.place_type IN ("Restaurant","Cafe") AND t1.distance<3000 and t1.property_id=t.property_id) as number_restaurants_3km,
    (select count(*) from property_poi_distances as t1 where t1.place_type IN ("Restaurant","Cafe") AND t1.distance<5000 and t1.property_id=t.property_id) as number_restaurants_5km  
from 
    property_poi_distances as t
group by t.property_id;

OUTPUT:

DEMO