First column value from multiple rows in postgres-CodePudding

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';

CodePudding user response：

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;