CODEWITHSUNDEEP
sqldatabase
rahul.cs
rahul.cs

Reputation: 55

Find total count of success and failed records

I have to formulate SQL Query to display total success and failed device processing. Suppose User selects 2 devices and runs some process. Each Process which user triggers will spawn 4 jobs (1 devices has 2 jobs to run). Since here user select 2 devices so 4 records comes in db. Now based on ParentTaskId I need to display total successfull,failed jobs with total devices.

We count a job on a device as success only when both jobs(Type 1,Type 2) are success.

Note : If jobtype 1 fails , jobtype 2 will not trigger

Ex:

Case 1:
ParentTaskId    DeviceId    TaskType    TaskStatus
101             a123         1          Success
101             a123         1          Success
101             a124         2          Success
101             a124         2          Success

Output : 
ParentTaskId    Success     Failed      TotalDevices
101              2           0              2

Case 2:
ParentTaskId    DeviceId    TaskType    TaskStatus
101             a123         1          Success
101             a123         1          Success
101             a124         2          Failed
101             a124         2          Success

Output:
ParentTaskId    Success     Failed      TotalDevices
101              1           1              2

Tried below approach but no success:

select
ParentTaskId,
COUNT(DISTINCT(DeviceId)),
count( case when TaskStatus='SUCCESS' then 1 end) as Success,
count( case when TaskStatus!='SUCCESS' then 1 end) as Failed
from XYZ 
where ParentTaskId = '101'
group by ParentTaskId,DeviceId

Any pointers/suggestions on this ?

Upvotes: 2

Views: 4340

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

You can use two levels of aggregation -- the inner one to get the status per parent and device. For this, you can actually use min(taskStatus) to get the status:

select ParentTaskId,
       sum(case when taskStatus = 'Success' then 1 else 0 end) as success,
       sum(case when taskStatus = 'Failed' then 1 else 0 end) as failed,
       count(*) as total
from (select ParentTaskId, deviceId, min(taskStatus) as taskStatus
      from t
      group by ParentTaskId, deviceId
     ) pd
group by ParentTaskId;

Upvotes: 1

Related Questions