finding total values for each month in postgres

Question

I need to find the total number of "wentto" trips for each month of the year . Assume all the months of 2010 are represented in the dataset.

Answer 1

You can use conditional aggregation:

select date_trunc('month', date) as tt,
       count(*) filter (where event = 'wentto')
from t
where date >= '2010-01-01' and date < '2011-01-01'
group by tt
order by tt;