Question 332051 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Number of companies with a given status per month starting on a custom day and time

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
Tags:

2

Answers


  1. 0

    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. 0

    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