Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 27-03-2009
Member
 
Join Date: Jan 2009
Posts: 66
A very difficult SQL statements into a master

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
Reply With Quote
  #2  
Old 27-03-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: A very difficult SQL statements into a master

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)

* /
Reply With Quote
  #3  
Old 27-03-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: A very difficult SQL statements into a master

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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags:



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "A very difficult SQL statements into a master"
Thread Thread Starter Forum Replies Last Post
Different statements in SQL query Kuna! Software Development 10 12-03-2011 04:02 PM
How to Inject SQL Statements in PHP? DANIEL 602 Software Development 5 05-03-2010 03:46 AM
Difference between DML statements and DDL statements Prashobh Mallu Software Development 5 11-01-2010 01:07 PM
Problem with my IF ELSE IF statements in C++ KACY5 Software Development 2 04-09-2009 05:12 PM
SQL statements with JSP blindsleeper Software Development 2 16-05-2009 09:54 PM


All times are GMT +5.5. The time now is 10:19 PM.