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

sql count distinct by col and sum false and true – Postgresql

How to query result as Expect Count with Sqlstatement

ID      StuId    IsDone    ExpectCount
11111   q-01     false        1         
11111   q-02     false        2      
11111   q-03     false        3        
11111   q-02     true         2   
11111   q-04     false        3   
22222   q-04     false        2
11111   q-01     true         1  
11111   q-01     true         1

expect if same ID StuId IsDone = true will ignore it.

11111 = 3
22222 = 1

but got

11111 = 1
22222 = 1

My query

SELECT ID, count(*)
FROM
(SELECT DISTINCT StuId, ID, IsDone FROM Student s where IsDone = false) stu
group by ID;
Tags:

2

Answers


  1. 0

    You can get the expected output by changing query as in this demo.

    SELECT ID, COUNT(DISTINCT StuId) - SUM(CASE WHEN IsDone = true THEN 1 ELSE 0 END) as ExpectedCount
FROM Student
GROUP BY ID;

    Output :

    id ExpectedCount
    11111 3
    22222 1
    Login or Signup to reply.
  2. 0

    The proper solution here is to do a 2 level aggregation. First, we can aggregate by both ID and StuId to determine which StuId have only false IsDone values. Then we aggregate by ID alone to get the pure false counts.

    WITH cte AS (
    SELECT ID, StuId, COUNT(*) FILTER (WHERE IsDone = true) cnt
    FROM Student
    GROUP BY ID, StuId
)

SELECT ID, COUNT(*) FILTER (WHERE cnt = 0) AS cnt
FROM cte
GROUP BY ID
ORDER BY ID;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search