First column value from multiple rows in postgres

Question

I am using postgres 11, and have below table

+----------+-------------------+-----------+-----------------------------+
| username |      filters      |   flag    |     time                    |
+----------+-------------------+-----------+-----------------------------+
| user1    | filter001         | 0         |2022-06-16 05:35:19.593000   |
| user1    | filter001         | 0         |2022-06-16 05:35:19.603000   |
| user1    | filter001         | 1         |2022-06-16 05:35:19.753000   |
| user1    | filter001         | 1         |2022-06-16 05:35:19.763000   |
| user1    | filter001         | 1         |2022-06-16 05:35:19.773000   |
| user1    | filter001         | 0         |2022-06-16 05:35:19.793000   |
| user1    | filter002         | 1         |2022-06-16 05:35:19.793000   |
| user1    | filter002         | 1         |2022-06-16 05:35:19.813000   |
| user1    | filter002         | 0         |2022-06-16 05:35:19.823000   |
| user1    | filter002         | 0         |2022-06-16 05:35:19.833000   |
| user1    | filter002         | 1         |2022-06-16 05:35:19.843000   |
| user1    | filter002         | 1         |2022-06-16 05:35:19.853000   |
| user1    | filter003         | 1         |2022-06-16 05:35:19.863000   |
| user1    | filter003         | 0         |2022-06-16 05:35:19.873000   |
| user1    | filter003         | 0         |2022-06-16 05:35:19.883000   |
| user1    | filter003         | 0         |2022-06-16 05:35:19.893000   |
| user1    | filter003         | 1         |2022-06-16 05:35:19.903000   |
| user1    | filter003         | 1         |2022-06-16 05:35:19.913000   |
| user1    | filter003         | 0         |2022-06-16 05:35:19.923000   |
| user1    | filter004         | 0         |2022-06-16 05:35:19.933000   |
| user1    | filter004         | 1         |2022-06-16 05:35:19.943000   |
| user1    | filter004         | 0         |2022-06-16 05:35:19.953000   |
| user1    | filter004         | 0         |2022-06-16 05:35:19.963000   |
| user1    | filter004         | 0         |2022-06-16 05:35:19.973000   |
+----------+-------------------+-----------------------------------------+

I'm trying to get below result from distinct filter value, along with count calculation

+-----------+----------+---------+---------------+----------------------------+
|  filters  | total_0  | total_1 | total_0_and_1 |    time                    |
+-----------+----------+---------+---------------+----------------------------+
| filter001 |    3     | 3       |      6        |2022-06-16 05:35:19.593000  |
| filter002 |    2     | 4       |      6        |2022-06-16 05:35:19.793000  |
| filter003 |    4     | 3       |      7        |2022-06-16 05:35:19.863000  |
| filter004 |    4     | 1       |      5        |2022-06-16 05:35:19.933000  |
+-----------+----------+---------+---------------+----------------------------+

I tried below query, which is giving me the desired result except for the time values, I am unable to add the time value to unique filters, time value could be the first record value of each filter. Any way to optimize and add time value of the first record of each filter?

select filters, 
       count(flag) filter (where flag=0) as total_0 ,
       count(flag) filter (where flag=1) as total_1, 
       count(time) as total_0_and_1 
from My_Table 
where username='user1';

Tim Biegeleisen · Accepted Answer · 2022-06-17 08:34:18Z

1

We can use COUNT as an analytic function along with DISTINCT ON here:

WITH cte AS (
    SELECT COUNT(flag) FILTER (WHERE flag = 0) OVER (PARTITION BY filters) AS total_0,
           COUNT(flag) FILTER (WHERE flag = 1) OVER (PARTITION BY filters) AS total_1, 
           COUNT(time) OVER (PARTITION BY filters) AS total_0_and_1,
           filters, time
    FROM My_Table 
    WHERE username = 'user1'
)

SELECT DISTINCT ON (filters) filters, total_0, total_1, total_0_and_1, time
FROM cte
ORDER BY filters, time;

edited Jun 17, 2022 at 8:34

answered Jun 17, 2022 at 8:00

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rakesh_parashar Over a year ago

Great, it worked, just filters needs to be added in the WITH clause, so it will be recognized

Tim Biegeleisen Over a year ago

@Rakesh Thanks, I have made that change in my answer.

Collectives™ on Stack Overflow

First column value from multiple rows in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related