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

Postgresql – Postgres query using filter and counts grouped by month

Below is the data table –

 del_no  |   Pkt   | direction  |  Env  | start_datetimestamp |
---------+---------+------------+-------+---------------------+
 H_00002 |  02     |  SOUTH     | PROD  | 2022-10-29 16:20:57 |
 E20     |  20     |  NORTH     | PROD  | 2022-10-30 16:41:37 |
 H_00002 |  02     |  NORTH     | TEST  | 2022-10-30 17:21:17 |
 E20     |  20     |  SOUTH     | DEV   | 2022-10-30 17:30:24 |
 H_00004 |  02     |  NORTH     | PROD  | 2022-10-30 16:52:48 |
 H_00004 |  02     |  SOUTH     | PROD  | 2022-10-30 19:03:36 |
 H_00007 |  02     |  NORTH     | PROD  | 2022-10-30 20:52:48 |
 H_00007 |  02     |  SOUTH     | PROD  | 2022-10-30 21:03:36 |
 H_00015 |  02     |  SOUTH     | TEST  | 2022-11-13 19:11:10 |
 L 0013  |  13     |  NORTH     | PROD  | 2022-11-14 20:06:46 |
 H_00015 |  02     |  NORTH     | TEST  | 2022-11-15 20:17:40 |
 L0021   |  21     |  SOUTH     | TEST  | 2022-11-15 20:56:18 |
 H_00015 |  02     |  NORTH     | PROD  | 2022-11-15 20:17:40 |
 L0027   |  21     |  SOUTH     | DEV   | 2022-11-30 20:56:18 |
 H_00019 |  02     |  NORTH     | PROD  | 2022-11-30 20:17:40 |
 L0023   |  21     |  SOUTH     | TEST  | 2022-11-30 20:56:18 |
 H_00019 |  02     |  SOUTH     | TEST  | 2022-11-30 20:17:40 |
 L0025   |  21     |  SOUTH     | TEST  | 2022-11-30 20:56:18 |
 H_00019 |  02     |  SOUTH     | DEV   | 2022-11-30 20:17:40 |

I want to count the number of rounds with below conditions –

1- Filter by Pkt where Pkt = 02 only.
2- Group by ‘del_no’ (consider NORTH and SOUTH one complete round), then counts all the rounds by monthly.
3- Segregate the counts based on Env. (e.g. 1st and 3rd row is one complete round and both has PROD and TEST env so that one count should be in PROD/TEST Env, same goes for other)

Output –

Month   |       Env     | Counts |
--------+---------------+--------+
 Oct    | PROD/TEST     |  1     |
 Oct    | PROD          |  2     |
 Nov    | PROD/TEST     |  1     |
 Nov    | PROD/TEST/DEV |  1     |

https://dbfiddle.uk/FGIzl6hy

Tags:

2

Answers


  1. 0

    You can use multiple CTEs, where First cte used to filter out any del_no with incomplete round, and second and third used to aggregate data based on month and Environment :

    with cte as (
  select del_no
  from tableName
  where Pkt = '2'
  group by del_no
  having count(distinct direction) = 2
),
cte2 as (
  select distinct to_char(start_datetimestamp, 'YYYY-MM') as _Month, t.del_no, Env
  from cte c
  inner join tableName t on c.del_no = t.del_no
),
cte3 as (
  select _Month, del_no, string_agg(distinct env, '/') as Env
  from cte2
  group by _Month, del_no
)
select _Month, env, count(env) as Counts 
from cte3
group by _Month, env

    Demo here

    Login or Signup to reply.
  2. 0 
    select "Month", sum(rounds)"Counts", "Env" from
(   select del_no,
           to_char(start_datetimestamp,'Mon') "Month",
           least( count(*)filter(where direction='SOUTH')
                 ,count(*)filter(where direction='NORTH')) rounds,
           string_agg(distinct env,'/' order by env) "Env"
    from tablename where pkt='2' group by del_no, 2
) group by "Month","Env";
    Month Counts Env
    Nov 1 DEV/PROD/TEST
    Nov 1 PROD/TEST
    Oct 2 PROD
    Oct 1 PROD/TEST

    db<>fiddle demo

    1. By default, Env are ordered alphabetically inside the field.
    2. Textual abbreviation of Month makes it less usable and sorts alphabetically. A date_trunc() would instead mark everything in October with a usable and sortable date of 2022-10-01, November as 2022-11-01, which could simplify further processing.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search