5.9 Aggregates and GROUP BY

5.48

select
    nta_name,
    count(ogc_fid)
from
    nyc_2015_tree_census
where
    spc_common like '%maple%'
group by
    nta_name
limit
    5

5.49

select
    nta_name,
    problems,
    count(ogc_fid)
from
    nyc_2015_tree_census
where
    spc_common like '%maple%'
group by
    nta_name,
    problems
limit
    5

5.50

select
    nta_name,
    array_agg(distinct curb_loc),
    count(ogc_fid)
from
    nyc_2015_tree_census
where
    spc_common like '%maple%'
group by
    nta_name
limit
    3

5.51

select
    nta_name,
    avg(stump_diam :: numeric)
from
    nyc_2015_tree_census
where
    stump_diam :: numeric > 0
group by
    nta_name
limit
    3

5.52

select
    passenger_count,
    avg(tip_amount) filter (
        where
            tip_amount > 5
    )
from
    nyc_yellow_taxi_0601_0615_2016
where
    pickup_datetime between '2016-06-10 15:00:00'
    and '2016-06-10 15:05:00'
group by
    passenger_count

5.53

select
    passenger_count,
    avg(tip_amount) filter (
        where
            tip_amount > 5
    ),
    count(ogc_fid)
from
    nyc_yellow_taxi_0601_0615_2016
where
    pickup_datetime between '2016-06-10 15:00:00'
    and '2016-06-10 15:05:00'
    and count(ogc_fid) > 50
group by
    passenger_count

5.54

select
    passenger_count,
    avg(tip_amount) filter (
        where
            tip_amount > 5
    ),
    count(ogc_fid)
from
    nyc_yellow_taxi_0601_0615_2016
where
    pickup_datetime between '2016-06-10 15:00:00'
    and '2016-06-10 15:05:00'
group by
    passenger_count
having
    count(ogc_fid) > 50

5.55

select
    passenger_count,
    tip_amount
from
    nyc_yellow_taxi_0601_0615_2016
where
    pickup_datetime between '2016-06-10 15:00:00'
    and '2016-06-10 15:05:00'
order by
    tip_amount desc
limit
    5

5.56

select
    nta_name,
    count(ogc_fid)
from
    nyc_2015_tree_census
where
    spc_common like '%maple%'
group by
    nta_name
order by
    count(ogc_fid) desc
limit
    5

5.57

select
    nta_name,
    count(ogc_fid)
from
    nyc_2015_tree_census
where
    spc_common like '%maple%'
group by
    nta_name
order by
    count(ogc_fid) desc
limit
    5 offset 5