16

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?

adolzi
  • 671
  • 2
  • 7
  • 15

3 Answers3

30

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.

jpw
  • 44,361
  • 6
  • 66
  • 86
  • 2
    Wow, amazing, thank you a lot! +1 for sample sql fiddle – adolzi May 06 '16 at 12:04
  • Do you need to use re-declare "extract(quarter from published_date)" again in the group by or order by statement? I.e. define `quarter` it in the select clause only. Because If I use the alias "quarter" in both the group by and order by clauses, my query still works. – Learner Feb 04 '21 at 04:24
  • @Learner It might be that it has changed in more recent versions, but I believe that at the time I wrote the answer it would have been necessary to do it that way to adhere to the standard, but I could be wrong. It probably comes down to how the database engine used parses the query and resolves aliases, i.e. if it's done in a single och multi pass fashion. That would be my guess anyway. – jpw Feb 04 '21 at 11:18
8

If you want both quarter and year you can use date_trunc:

SELECT
  date_trunc('quarter', published_date) AS quarter

This gives the date rounded to the start of the quarter, e.g. 2020-04-01, and has the advantage that subsequent steps in the pipeline can read it like a normal date.

(This compares to extract (extract(quarter FROM published_date)), which gives the number of the quarter, i.e. 1, 2, 3, or 4.)

Derek Hill
  • 5,965
  • 5
  • 55
  • 74
  • Agree this is useful. As the date isn't lost, just truncate to the quarter start date. Useful if your data spans multiple years. – Learner Feb 04 '21 at 04:26
3

If someone also needs the year, they can use this:

SELECT (extract(year from published_date)::text || '.Q' || extract(quarter from published_date)::text) as quarter

This will return the value in the form 2018.Q1.

How does it work? It extracts both year and quarter (which are both of type double precision), casts them into strings, and concatenates the whole in something readable.

Fabien Snauwaert
  • 4,995
  • 5
  • 52
  • 70