Cacher is the code snippet organizer for pro developers

Retrieving Month from created_at attribute

SELECT to_char(created_at, 'Month') as mon,
  extract(year from created_at) as yyyy,
  COUNT(*) as "Total"
FROM table_name
WHERE extract(year from created_at) = extract(year from now())
GROUP BY 1,2;

NOTES

• to_char() converts the created_at attribute into the defined format of the month, use 'Mon' fior for the shorter month name version.
• extract() is used to retrieve subfields from an attribute. For example: year, month, etc. from the specified attribute.
• use COUNT(*) to retrieve the count of all records within that month and year
• the WHERE clause is used to specify records from the current year!
• the group by clause is used to organize the results based on all the columns specified in the select clause. 1, 2; is a shorthand notation for the column 1 and column 2 in the select statement otherwise for better readability replace 1, 2; with to_char()..., extract()... the 2 columns in the select statement.

Months as Columns for Query

select *, 
  COUNT(CASE WHEN month = 1 THEN 1 END) as january,
  ...,
  COUNT(CASE WHEN month = 12 THEN 1 END) as december
  from (
    select *, EXTRACT(month from created_at) as month
    from ...
    join ...
    where
  ) table_name
  group by 1;