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 |
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 |
Env are ordered alphabetically inside the field.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.