A very difficult SQL statements into a master

[Xanti]

I have an exam table, my function is to want to make this table the error rate table is structured as follows:

id subjectid (foreign key, point to questions table here) content (the content of examination questions) is right (Whether or not right, 0 errors, 1 right)
1 101 subject content 0
2 101 subject content 0
3 101 subject of the contents of one
4 101 subject content of one
5 102 title of the contents of 0
6 102 Title 1
7 103 topics content 0
8 103 Title 1
9 103 Title 1


My idea is: first according to what subjectid group by group, and then concludes that the number of errors for each subject, but also appeared to draw the total number of each topic, (such as example of this is drawn on the total number of 101 errors for the 4 for two, so that the result will be displayed by)
And what sort, many of the mistake of standing in the front is equal order by clicking the wrong number,i did not want to for a long time to write the SQL to be grateful

[opaper]

create table tb (id int, subjectid int, content varchar (10), isright int)
insert into tb values (1, 101, 'subject content', 0)
insert into tb values (2, 101, 'subject content', 0)
insert into tb values (3, 101, 'subject content', 1)
insert into tb values (4, 101, 'subject content', 1)
insert into tb values (5, 102, 'subject content', 0)
insert into tb values (6, 102, 'subject content', 1)
insert into tb values (7, 103, 'subject content', 0)
insert into tb values (8, 103, 'subject content', 1)
insert into tb values (9, 103, 'subject content', 1)
go

select subjectid,
[Error rate (%)] = cast (sum (case isright when 0 then 1 else 0 end) * 100.0 / (Select count (1) from tb where subjectid = t.subjectid) as decimal (18, 2))
from tb t
group by subjectid
order by [Error rate (%)] desc, subjectid

drop table tb

/ *
subjectid error rate (%)
----------- --------------------
101 50.00
102 50.00
103 33.33

(Affected by the number of rows for the 3 lines)

* /

[MindSpace]

select subjectid, content, count (*) as the title number,
sum (case when isright = 0 then 1 else 0 end) as an error quantity,
cast (sum (case when isright = 0 then 1 else 0 end) / count (*) * 100 as char (2)) + '%' as the error rate from tb
group by subjectid, content
order by sum (case when isright = 0 then 1 else 0 end) desc