Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Unanswered: Calculating average for each student SQL and get the highest

    I want to calculate average of grades of each student and get the highest one with SQL command.
    I have 2 tables:


    Students:
    *StudentId
    *StudentName
    ___________
    Grades:
    *StudentId
    *Grade
    ___________
    I need to calculate average of each student and then get the highest.
    My try:
    Code:
    SELECT Students.StudentId,Students.StudentName,AVG(Grades.Grade) AS avg_grade FROM Students s JOIN Grades g ON Grades.StudentId =Students.StudentId
    GROUP BY Students.StudentId, Students.StudentName
    
    ORDER BY avg_grade
    
    LIMIT 1 FROM Students;
    I encounter problem with this code,maybe it`s Completely wrong...

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't order by "avg_grade" because its an alias, and is not applied to the dataset until after the calculations are complete.
    So you need to order by "AVG(Grades.Grade)"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. Here are my guesses that I had to make:

    CREATE TABLE Students
    (student_id CHAR(10) NOT NULL PRIMARY KEY,
    student_name VARCHAR(35) NOT NULL);

    I used a common length for the assumed key. I picked the USPS length for an address line for the name.

    CREATE TABLE Gradebook
    (student_id CHAR(10) NOT NULL
    REFERENCES Student(student_id),
    test_nbr SMALLINT NOT NULL
    CHECK(test_nbr > 0),
    test_grade DECIMAL (5,2) NOT NULL
    CHECK(course_grade BETWEEN 0.00 AND 100.00));

    The table is a set of grades; it is not the grades. It needs a collective name! Grades are a value on a scale. But since you did not follow minimal polite behavior, I will guess this is a 0-100 scale instead of a letter system {'A', 'B', 'C', 'D', 'F', 'I', 'W'}

    >> I need to calculate average of each student and then get the highest. <<

    English is a terrible language in so many ways. But my desire to post in Esperanto will not be popular . How many grades does a student have in the grade book? That is why we need DDL! Taking the average of one grade per student is useless, but then your non-DDL narrative has no key.

    So I had to assume a student has many tests when I did DDL for you.

    WITH Averaged_Grades (student_id, student_name, grade_avg)
    AS
    (SELECT SELECT S.student_id, S.student_name, AVG(G.test_grade)
    FROM Students AS S, GradeBook AS G
    WHERE G.student_id = S.student_id
    GROUP BY S.student_id, S.student_name)

    SELECT G1.student_id, G1.student_name, G1.grade_avg
    FROM Averaged_Grades AS G1
    WHERE G1.grade_avg
    = (SELECT MAX(G2.grade_avg)
    FROM Averaged_Grades AS G2);

    The idea is to first use a CTE to average each student. Find the maximum average fromteh CTE, then find students with that average. In the real world, I would allow an epsilon of, say 0.10 or 0.05 for rounding errors.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by blindman View Post
    You can't order by "avg_grade" because its an alias, and is not applied to the dataset until after the calculations are complete.
    So you need to order by "AVG(Grades.Grade)"
    Really?
    ORDER BY will be processed after SELECT.
    So, please try
    ORDER BY avg_grade DESC
    and
    TOP 1

    SELECT (Transact-SQL)
    Logical Processing Order of the SELECT statement
    The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

    1.FROM

    2.ON

    3.JOIN

    4.WHERE

    5.GROUP BY

    6.WITH CUBE or WITH ROLLUP

    7.HAVING

    8.SELECT

    9.DISTINCT

    10.ORDER BY

    11.TOP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I stand (or sit, actually) corrected!
    aviv, there are couple problems with your sql.
    First, LIMIT is not supported in TSQL.
    Second, when you listed your column names you included the name of the table, but you aliased the table names in your FROM clause. You need to either not alias the table names (preferred), or refer to the columns by their alias name in your SELECT clause. Unnecessary aliasing is (IMO), sloppy and leads to obfuscated code.

    To get the student with the highest average grade:
    Code:
    SELECT	top 1
    	Students.StudentId,
    	Students.StudentName,
    	AVG(Grades.Grade) AS avg_grade
    FROM	Students
    	INNER JOIN Grades ON Grades.StudentId =Students.StudentId
    GROUP BY Students.StudentId, 
    	Students.StudentName
    ORDER BY AVG(Grades.Grade) desc
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2013
    Posts
    2

    Thank You!

    It works thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •