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-17T08:34:18.727

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 '22 at 08:34

answered Jun 17 '22 at 08:00

Tim Biegeleisen

502,043
27
286
360

1

Great, it worked, just filters needs to be added in the WITH clause, so it will be recognized – name_doesnt_matters Jun 17 '22 at 08:34
@Rakesh Thanks, I have made that change in my answer. – Tim Biegeleisen Jun 17 '22 at 08:34

First column value from multiple rows in postgres

1 Answers1