Iterate through SQL result set adding subquery results to result set-CodePudding

I have a table [Robots] containing Robot IDs, and a [TestResults] table which has a FK to [Robots] of [RobotId]

What I am aiming to get is a result set of each robotID in the [Robots] table and for each ID a total of all entries in the [TestResults] of Status 'Active', 'Failed' and total of all status, I have been stuck on the approach for this.

eg:

|RobotId 'R2-D2'|ActiveNum:8|FailedNum:12|TotalNum:20|
|RobotId 'C3P0'|ActiveNum:4|FailedNum:2|TotalNum:9|

I need a method to iterate through the IDs and perform these queries as below

Select RobotID from Robots Order by RobotID ASC

--iterate through each ID from above result set executing the three queries below

--Get total where test status is active
SELECT count(*) as ActiveNum 
FROM TestResults
WHERE testStatus = 'active' AND RobotID = 'XX'

--Get total where test status is failed
SELECT count(*) as FailedNum 
FROM TestResults
WHERE testStatus = 'failed' AND RobotID = 'XX'

--Get total of all status
SELECT count(*) as TotalNum 
FROM TestResults
WHERE RobotID = 'XX'

CodePudding user response：

Just join and conditionally sum e.g.

select RobotID
    , sum(case when tr.testStatus = 'Active' then 1 end) ActiveNum
    , sum(case when tr.testStatus = 'Failed' then 1 end) FailedNum
    , count(*) TotalNum
from Robots r
left join TestResults tr on tr.RobotID = r.RobotID
group by RobotID asc
order by RobotID asc;

Typically if you find yourself considering loops in SQL you've taken a wrong turn. Its a set based language so needs to be approached as such.

Note the left join just in case a robot has no test results, it will still show in the list. If this isn't the desired behaviour, change it to an inner join.