Crosstab in PostgreSQL - How to show aggregates by year in columns?

user_14271488 0
Viewed 74

I'm new to SQL. I have unioned several tables together where data is stored separately by year. I am attempting to count the number of rows per unique ID by year. My current query is:

SELECT start_station_id,
date_part('year',start_time),
COUNT(*)
FROM public.capitalbikeshare_2016
WHERE user_type = 'Casual'
GROUP BY 1, 2
UNION ALL
SELECT start_station_id,
date_part('year',start_time),
COUNT(*)
FROM public.capitalbikeshare_2017
WHERE user_type = 'Casual'
GROUP BY 1, 2
UNION ALL
...

and outputs as:

unique id | date_part | count
id1          2016        #
id1          2017        #
id1          2018        #
id1          2019        #
id2          2016        #
...

I have tried count if/filters but have been unsuccessful at transposing the years to columns. I'm hoping to transpose them to columns so that I can then calculate the year over year growth.

pivot count union postgresql sql
1 Answers

One option uses union all, then conditional aggregation:

select start_station_id, 
    count(*) filter(where start_time >= date '2016-01-01' and start_time < date '2017-01-01') cnt_2016,
    count(*) filter(where start_time >= date '2017-01-01' and start_time < date '2018-01-01') cnt_2017,
    count(*) filter(where start_time >= date '2018-01-01' and start_time < date '2019-01-01') cnt_2018,
    count(*) filter(where start_time >= date '2019-01-01' and start_time < date '2020-01-01') cnt_2019
from (
    select start_station_id, start_time from public.capitalbikeshare_2016 where user_type = 'Casual'
    union all
    select start_station_id, start_time from public.capitalbikeshare_2017 where user_type = 'Casual'
) t
group by start_station_id
user_106767160
Related