Categories

See More
Popular Forum

MBA (4887) B.Tech (1769) Engineering (1486) Class 12 (1030) Study Abroad (1004) Computer Science and Engineering (988) Business Management Studies (865) BBA (846) Diploma (746) CAT (651) B.Com (648) B.Sc (643) JEE Mains (618) Mechanical Engineering (574) Exam (525) India (462) Career (452) All Time Q&A (439) Mass Communication (427) BCA (417) Science (384) Computers & IT (Non-Engg) (383) Medicine & Health Sciences (381) Hotel Management (373) Civil Engineering (353) MCA (349) Tuteehub Top Questions (348) Distance (340) Colleges in India (334)
See More

Getting top(N) in database by group, where n is read from database in sqlite

Course Queries Syllabus Queries

Max. 2000 characters
Replies

usr_profile.png

User

( 4 months ago )


I am looking to solve a variant of the "select top(N) by group" question, but my problem is that N has to be looked up in the database. I have a way of doing it, but am hoping to find a clearer way.

If it helps, I am using sqlite3.

I am designing a course, and have a table that assigns the weights to be stored in the table gradeSyllabus:

title       weight      dropLowest
----------  ----------  ----------
Homework    0.6         0         
Test        0.4         1

i.e. Homeworks make up 60% of the grade with none dropped, while tests make up 40% of the grade with the lowest test dropped.

Here is some fake student data from the table gradeGrades:

studentId   assess_title  assess_num  score     
----------  ------------  ----------  ----------
john345     Homework      1           75.0      
john345     Homework      2           85.0      
john345     Homework      3           64.0      
john345     Test          1           75.0      
john345     Test          2           85.0      
john345     Test          3           64.0      
mary111     Test          1           78.0      
mary111     Test          2           72.0      
mary111     Test          3           84.0      
mary111     Homework      1           90.0      
mary111     Homework      2           92.0      
mary111     Homework      3           88.0

I can find the average for each student easily enough:

SELECT GG.studentId, assess_title, avg(score) as overall, weight
   FROM gradeGrades GG INNER JOIN gradeSyllabus GS
   ON (assess_title = title)
   GROUP BY GG.studentId, assess_title;

This gives the following results:

studentId   assess_title  overall           weight    
----------  ------------  ----------------  ----------
john345     Homework      74.6666666666667  0.6       <-- correct
john345     Test          74.6666666666667  0.4       
mary111     Homework      90.0              0.6       <-- correct
mary111     Test          78.0              0.4 

The tests need the lowest test dropped. In general, I cannot use min (because we may change the syllabus to drop the lowest 2 tests). Here is a query that addresses this problem:

SELECT studentId, assess_title, sum(score) / count(*) as overall
   FROM gradegrades G1
        WHERE (select count(*) from gradegrades as G2
                   WHERE G1.studentid = G2.studentId AND
                         G1.assess_title = G2.assess_title AND
                         G2.score <= G1.score) -- select lowest
                         > (SELECT dropLowest FROM gradeSyllabus
                            WHERE gradeSyllabus.title = G1.assess_title)
   GROUP BY studentId, assess_title;

This produces:

studentId   assess_title  overall         
----------  ------------  ----------------
john345     Homework      74.6666666666667  
john345     Test          80.0            
mary111     Homework      90.0            
mary111     Test          81.0  

where all the overall columns are correct.

My questions:

1) Can I get the weight from the gradeSyllabus table from the subselect? Or do I need another join?

2) Is there a better (i.e. clearer) way of generating this table in the first place? Efficiency would be nice, but at the moment my tables are rather small (~ 30 students)

3) Are there (obvious?) problems with the query?

what's your interest


forum_ban8_5d8c5fd7cf6f7.gif