Clash Royale CLAN TAG#URR8PPP

MS SQL calculating percentage in Derived table

i'm trying to work with 3 tables and create a derived table to get some data together that shows a percentage of completion.
The 3 tables I have that i'm working with are
Student, Tests and Results. I'm trying to join the 3 together and create a derived table that shows the student and the progress they have made, in a percentage of tests completed.

As an example, lets assume the 3 students I want to track have all been assigned 3 tests (out of a table with hundreds) and I want to see how far along they are. If they completed all 3 tests the derived table should store the value 100%.

**StudentID, SName**
1 Ken
2 Tom
3 Bob

**TestID, TName**
11 Test 101
22 Test 102
33 Test 103

**ResultsID, TestID, StudentID, Passed**
1 11 Tom 0
2 11 Bob 1
3 22 Bob 1
4 33 Bob 1


Derived Table:

StudentID, SName, %Completed
1 Ken 0%
2 Tom 0%
3 Bob 100%


I have tried a lot of different methods and don't know which one to even show because I feel like all the attempts have been completely wrong. Any ideas? Sorry if the formatting isn't great, it's my first post here :)

Thanks!

You should show your attempts.
– Gordon Linoff
1 hour ago

2 Answers
2

This seems like a join and group by with a twist:

join

group by

select s.StudentID, s.SName,
sum(case when r.passed = 1 then 1.0 else 0 end) / t.cnt
from students s left join
results r
on s.studentid = r.studentid cross join
(select count(*) as cnt
from tests
) t
group by s.StudentID, s.SName, t.cnt;


Thank you Gordon. I have tried your suggested query with the addition of the sum(case when a.CompletionStatus = 1 then 1.0 else 0 end) / nullif(t.cnt,0) to prevent a divide by zero error, however the column name comes in as blank instead of as cnt for some reason.
– KenM
26 mins ago

To get all possible combinations of students with the tests.

One could cross join to a sub-query of the tests the students should pass.

Then all possible combinations of student/test can be left joined to the actual results.

After that, it's a simple GROUP BY with an average.

select
stu.StudentID,
stu.SName,
concat(AVG(coalesce(res.Passed,0)*100),'%') as [%Completed]
from Students as stu
cross join (
select TestID, TName
from Tests
where TestID in (11,22,33)
) as tst
left join Results as res on (res.StudentID = stu.StudentID and res.TestID = tst.TestID)
group by stu.StudentID, stu.SName


Example snippet:

-- Sample data
-- Using table variables for demonstration
declare @Students table (StudentID int identity(1,1) primary key, SName varchar(30));
insert into @Students (Sname) values
('Ken'),
('Tom'),
('Bob'),
('Jane');
declare @Tests table (TestID int primary key, TName varchar(30));
insert into @Tests (TestID, TName) values
(11,'Test 101'),
(22,'Test 102'),
(33,'Test 103');
declare @Results table (ResultsID int identity(1,1) primary key, TestID int, StudentID int, Passed bit);
insert into @Results (TestID, StudentID, Passed) values
(11,2,0),
(11,3,1),(22,3,1),(33,3,1),
(11,4,1);

-- Query
select
stu.StudentID,
stu.SName,
concat(AVG(coalesce(res.Passed,0)*100),'%') as [%Completed]
from @Students as stu
cross join (
select TestID, TName
from @Tests
where TestID in (11,22,33)
) as tst
left join @Results as res on (res.StudentID = stu.StudentID and res.TestID = tst.TestID)
group by stu.StudentID, stu.SName;


Result:

StudentID SName %Completed
--------- ----- ----------
1 Ken 0%
2 Tom 0%
3 Bob 100%
4 Jane 33%


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.