CODEWITHSUNDEEP
sqlsql-server
mandarin software
mandarin software

Reputation: 217

Need count record with special condition

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

Upvotes: 2

Views: 51

Answers (1)

shawnt00
shawnt00

Reputation: 17915

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

Upvotes: 2

Related Questions