2

I have this Ruby code:

def visits_chart_data(domain)
  last_12_months.collect do |month|
    { created_at: month, count: domain.visits.created_on_month(month).count }
  end
end

def last_12_months
  (0..11).to_a.reverse.collect{ |month_offset| month_offset.months.ago.beginning_of_month }
end

CreatedOnMonth is just a scope:

scope :created_on_month, -> (month = Time.now) { where{ (created_at >= month.beginning_of_month) & (created_at <= month.end_of_month) } }

created_at is just a standard datetime timestamp.

How can I optimize it to make one query instead of 12?

I saw some people use GROUP_BY, but I'm not that good with PostgreSQL to be able to build such query myself. The query should group records by month of the year and return count. Maybe someone could help me out. Thanks.

Serge Vinogradoff
  • 2,262
  • 4
  • 26
  • 42
  • Possible duplicate of http://stackoverflow.com/questions/17492167/group-query-results-by-month-and-year-in-postgresql – Max Williams May 20 '14 at 11:00
  • possible duplicate of [Selecting by month in PostgreSQL](http://stackoverflow.com/questions/8863156/selecting-by-month-in-postgresql) – Mike Szyndel May 20 '14 at 11:06

1 Answers1

9

Use the date_trunc() function in your GROUP BY clause, if you want to group by each month of each year:

GROUP BY date_trunc('month', created_at)

Use EXTRACT() function in your GROUP BY clause, if you want to group by each month in every year:

GROUP BY EXTRACT(MONTH FROM created_at)
pozs
  • 34,608
  • 5
  • 57
  • 63
  • Thanks - i deleted my comment as i realised date_trunc does what is required but it's good to have the clarification :) – Max Williams May 20 '14 at 11:02