Query with COUNT function with monthly breakdown?

Question

PostgreSQL

Guys, good afternoon, I
need help with a SELECT query
, there is a table with reports:

create table new_1report
(
    id_report  serial not null
        constraint new_1report_pkey
            primary key,
    id_form    integer
        constraint new_1report_id_form_fkey
            references new_form,
    admin  varchar(40),
    created_at timestamp default now(),
);

I'm struggling with the output of the number of reports of each admin, broken down by month, something like this:

fio Jan Feb Mar
Ivanov I 5 3 8
Petrov A 7 10 13
....

plz help with solution
PS: I use this query:

SELECT COUNT(admin_id), admin_id, a.admname
FROM new_1report f
        JOIN admin a ON f.admin_id = a.id
group by admin_id, a.admname

But I would not want to make separate queries for each month

Answer 1

You can do this, and then parse it on the application side

SELECT COUNT(admin_id), admin_id, a.admname, date_part('month', f.created_at)
FROM new_1report f
        JOIN admin a ON f.admin_id = a.id
group by admin_id, a.admname, date_part('month', f.created_at)

Answer 2

group by decision