Need count record with special condition

user_14390377 0
Viewed 44

I have this table in my database:

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

I need count Value1 to get the result that contain of 0 and the max value with group by Type.

I have tried to do this query:

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)

The result is :

 p3          process1 process

   Developer    0       2
   programmer   5       0

But I need the result for (Programmer = 3 Not 5) because I want to count just: these result:

   3    programmer  0       process1
   4    programmer  0       process1
   5    programmer  30      process1
sql-server sql
1 Answers
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
user_13222680
Related