skip to Main Content

I have the following PostgreSQL table:

create table companies (
        id               bigserial primary key,
        created_at       timestamp(6),  
        name             varchar(200),   
        status           varchar(200)
);

I would like to split the result by month and to pass the months by param.
How I can get the following result using SQL query:

created_at Active Blocked
2023-8-07 18:06:56.000000 54 32
2023-5-07 18:06:56.000000 51 13
2023-12-07 18:06:56.000000 35 35

2

Answers


  1. You can use date_trunc() in combination with an aggregate filter clause: demo

    select date_trunc('month',created_at-'6d 18:06:56'::interval)
                                        +'6d 18:06:56'::interval as created_at,
           count(*)filter(where status='Active') as "Active",
           count(*)filter(where status='Blocked') as "Blocked"
    from companies
    group by 1 order by 1;
    

    It subtracts the custom month start, aligns the result to the beginning of the month, restores the start point. If created_at was on or after that custom start point, it’ll stay in its month. Otherwise it’ll be assigned to the previous one.

    Based on this:

    id created_at name status
    1 2023-10-07 18:06:55.9 Right before custom October start Active
    2 2023-10-07 18:06:56.01 Right after custom October start Blocked
    3 2022-11-03 17:53:38.894278 a5b4172 Blocked
    4 2022-10-03 14:52:14.827078 2bfc05d9-25f4- Active

    It returns this:

    created_at Active Blocked
    2022-09-07 18:06:56 1 0
    2022-10-07 18:06:56 0 1
    2023-09-07 18:06:56 1 0
    2023-10-07 18:06:56 0 1
    Login or Signup to reply.
  2. so you want to group by month and count on each month how many are active and how many are blocked?

    you can do this with

    SELECT 
    DATEPART(month, created_at) AS Monthly,
    count(case when status = 'Active' then 1 else null end) as Active,
    count(case when status = 'Blocked' then 1 else null end) as Blocked,
    FROM companies
    GROUP BY DATEPART(month, created_at)
    

    if you want to group it based on year-month:

    SELECT 
    FORMAT(created_at, 'yyyy_MM') AS year_month,
    count(case when status = 'Active' then 1 else null end) as Active,
    count(case when status = 'Blocked' then 1 else null end) as Blocked,
    FROM companies
    GROUP BY FORMAT(created_at, 'yyyy_MM')
    

    hope this helps

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search