Postgres group by quarter

Question

I have table with columns: topic, person, published_date. I would like to create query which help me compute how many times every person wrote in specific topic in every quarter. Example:

topic person published_date

'world'  JS   2016-05-05 
'world'  JS   2016-05-10
'nature'  AR   2016-12-01

should return something like

topic person quarter how_many_times
'world'  JS     2          2
'nature' AR     4          1

I'm able to group it by topic and person

select topic, person, published_date, count(*) from table group by topic, person, published_date

but how group published_date into quarters?

Answer 1

Assuming that the published_date is a date type column you can use the extract function like this:

select 
  topic, 
  person,
  extract(quarter from published_date) as quarter, 
  count(*)
from 
  table1
group by 
  topic, 
  person,
  extract(quarter from published_date)
order by 
  extract(quarter from published_date) asc

Sample SQL Fiddle

If the dates can fall into different years you might want to add the year to the select and group by.