sql - 需要特殊条件的计数记录

Question

我的数据库中有这个表:

num     p3         Value1   p22
  
   1    Developer1  0       process
   2    Developer1  0       process
   3    programmer  0       process1
   4    programmer  0       process1
   5    programmer  30      process1
   6    programmer  20      process1
   7    programmer  10      process1

我需要 count Value1 来获得包含 0 和最大值的结果，按类型分组。
我试图做这个查询:

SELECT p3,
       SUM(CASE WHEN p22 IS NOT NULL and p22 ='process1' THEN 1 ELSE 0 END) AS process1,
       SUM(CASE WHEN p22 IS NOT NULL and p22 ='process' THEN 1 ELSE 0 END) AS process
FROM add_patient
group by (p3) 

结果是:

 p3          process1 process

   Developer    0       2
   programmer   5       0

但我需要 (Programmer = 3 Not 5) 的结果，因为我只想计算:这些结果:

   3    programmer  0       process1
   4    programmer  0       process1
   5    programmer  30      process1

Answer 1

with data as (
    select *, max(value1) over (partition by p3) as maxp3
    from add_patient
)
SELECT p3,
       SUM(CASE WHEN p22 = 'process1' AND value1 IN (0, maxp3)
                THEN 1 ELSE 0 END) AS process1,
       SUM(CASE WHEN p22 = 'process'  AND value1 IN (0, maxp3)
                THEN 1 ELSE 0 END) AS process
FROM data
GROUP BY p3