CODEWITHSUNDEEP
mysqlsqlsubquery
user2876414
user2876414

Reputation: 25

SQL query using nested subquery and GROUP BY

I have written a query which calculates student's GPAs from information across multiple tables:

SELECT Major DNO, DName Dept, FName, LName,
    (SUM(g.gradepoint*c.Credits)/SUM(c.Credits)) GPA
FROM Student s, Enrolled_in e, Gradeconversion g, Course c, Department d
WHERE s.StuID=e.StuID and e.Grade=g.lettergrade and e.CID=c.CID and d.DNO=s.Major
GROUP BY e.StuID;

which produces this output:

+------+-----------------------+---------+----------+------------------+
| DNO  | Dept                  | FName   | LName    | GPA              |
+------+-----------------------+---------+----------+------------------+
|  600 | Computer Science      | Linda   | Smith    |  3.3187500089407 |
|  600 | Computer Science      | Tracy   | Kim      | 3.08235291873708 |
|  600 | Computer Science      | Shiela  | Jones    | 3.05999999046326 |
|  600 | Computer Science      | Dinesh  | Kumar    | 2.78947370930722 |
|  600 | Computer Science      | Paul    | Gompers  | 2.89999998699535 |
|  600 | Computer Science      | Andy    | Schultz  | 2.98823530533734 |
|  600 | Computer Science      | Lisa    | Apap     | 3.32105263910796 |
|  600 | Computer Science      | Jandy   | Nelson   |              3.2 |
|  600 | Computer Science      | Eric    | Tai      | 3.01923077840071 |
|  600 | Computer Science      | Derek   | Lee      | 3.61304346374843 |
|  600 | Computer Science      | David   | Adams    |              3.3 |
|  600 | Computer Science      | Steven  | Davis    | 3.18750002980232 |
|  600 | Computer Science      | Charles | Norris   | 3.57142857142857 |
|  600 | Computer Science      | Susan   | Lee      |  3.5071428673608 |
|  600 | Computer Science      | Mark    | Schwartz |  2.9434782733088 |
|  600 | Computer Science      | Bruce   | Wilson   | 3.05789474437111 |
|  600 | Computer Science      | Michael | Leighton | 3.17058825492859 |
|  600 | Computer Science      | Arthur  | Pang     | 2.89999998699535 |
|  520 | ECE                   | Ian     | Thornton |                4 |
|  520 | ECE                   | George  | Andreou  | 2.94782609524934 |
|  540 | Chemical Engineering  | Michael | Woods    | 3.26666665960241 |
|  520 | ECE                   | David   | Shieber  |            3.375 |
|  540 | Chemical Engineering  | Stacy   | Prater   | 3.06666667373092 |
|  520 | ECE                   | Mark    | Goldman  | 3.42307692307692 |
|  520 | ECE                   | Eric    | Pang     |  3.7562500089407 |
|  520 | ECE                   | Paul    | Brody    | 2.90526317295275 |
|  550 | Mathematical Sciences | Eric    | Rugh     | 3.82499998807907 |
|  100 | History               | Jun     | Han      | 3.10500003099442 |
|  550 | Mathematical Sciences | Lisa    | Cheng    | 2.95384616118211 |
|  550 | Mathematical Sciences | Sarah   | Smith    | 3.09230767763578 |
|  550 | Mathematical Sciences | Eric    | Brown    | 2.98000001907349 |
|  550 | Mathematical Sciences | William | Simms    |              3.8 |
|   50 | Cognitive Science     | Eric    | Epp      | 3.11249998211861 |
|   50 | Cognitive Science     | Sarah   | Schmidt  | 3.08125002682209 |
+------+-----------------------+---------+----------+------------------+

Now I need to find the tuples in this table which correspond to the students with the lowest GPA in each major My thought was to do something like this:

SELECT DNO, Dept, FName, LName, MIN(GPA) GPA FROM
    (SELECT Major DNO, DName Dept, FName, 
        LName,(SUM(g.gradepoint*c.Credits)/SUM(c.Credits)) GPA
    FROM Student s, Enrolled_in e, Gradeconversion g, Course c, Department d
    WHERE s.StuID=e.StuID and e.Grade=g.lettergrade and e.CID=c.CID and d.DNO=s.Major
    GROUP BY e.StuID) p
GROUP BY Dept;

but that produces:

+------+-----------------------+---------+----------+------------------+
| DNO  | Dept                  | FName   | LName    | GPA              |
+------+-----------------------+---------+----------+------------------+
|  540 | Chemical Engineering  | Michael | Woods    | 3.06666667373092 |
|   50 | Cognitive Science     | Eric    | Epp      | 3.08125002682209 |
|  600 | Computer Science      | Linda   | Smith    | 2.78947370930722 |
|  520 | ECE                   | Ian     | Thornton | 2.90526317295275 |
|  100 | History               | Jun     | Han      | 3.10500003099442 |
|  550 | Mathematical Sciences | Eric    | Rugh     | 2.95384616118211 |
+------+-----------------------+---------+----------+------------------+

The minimum GPA in each department is found, but it is associated with whatever student fname and lname happened to be listed first in that department, and not the fname and lname that actually had the lowest GPA. I know that when you use GROUP BY, every attribute in the SELECT that isn't part of the aggregate function should appear in the GROUP BY. I'm just not sure how to proceed with this query to get the values I'm looking for. Any help would be greatly appreciated as I'm quite new at this!

Upvotes: 2

Views: 122

Answers (1)

geomagas
geomagas

Reputation: 3280

You need yet another level of nesting and another JOIN to accomplish that. Using a VIEW would provide you with reusability and easy query building.

So as a first step, store your original query in a view like so:

CREATE VIEW GPAS AS
SELECT Major DNO, DName Dept, FName, LName,
    (SUM(g.gradepoint*c.Credits)/SUM(c.Credits)) GPA
FROM 
  Student s, Enrolled_in e, Gradeconversion g, Course c, Department d
WHERE s.StuID=e.StuID and e.Grade=g.lettergrade 
and e.CID=c.CID and d.DNO=s.Major
GROUP BY e.StuID;

Now, to reproduce the first result you mentioned, is as easy as doing 

select * from gpas

Now to the point: Apparently, your second query would be rewritten as 

SELECT DNO, Dept, FName, LName, MIN(GPA) GPA 
FROM GPAS p ORDER BY Dept.

But it would still return the same results, right? To achieve what you want, do:

select * from gpas g
join (select dept,MIN(GPA) mingpa from gpas group by dept) mingpas mg
on (g.dept=mg.dept and g.GPA=mg.mingpa)

You can see how easier and more comperhensive this gets using views. But, should you decide not to use them, replace every FROM GPAS in the above queries with 

FROM (YourOriginalQuery) AS somealias

Upvotes: 2

Related Questions