Question 304698 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Ranking students based on average ranges in SQL

I am working on a ranking system for students’ averages in SQL. The goal is to assign positions based on average ranges, not just for those with exactly the same average.

For example, students with averages between 90 to 90.9 should have the 4th position. If the closest average after the 4th position is 85, then averages between 85 to 85.9 would be the 5th position.

I have tried using the DENSE_RANK() window function along with a CASE statement, but I’m having trouble implementing the logic for ranking based on ranges.

This is my current SQL query:

WITH RankedAverages AS (
  SELECT
    CAST(AVG(ft_tot_score) AS DECIMAL(10, 2)) AS unique_average,
    DENSE_RANK() OVER (ORDER BY AVG(ft_tot_score) DESC) AS dense_rank
  FROM
    ftscores_primary
  WHERE
    class_id = 9 AND
    section_id = 3 AND
    session_id = 19
  GROUP BY
    student_id
  ORDER BY
    unique_average DESC
)

SELECT
  unique_average,
  CASE
    WHEN dense_rank = 1 THEN '1st'
    WHEN dense_rank = 2 THEN '2nd'
    WHEN dense_rank = 3 THEN '3rd'
    ELSE
      CASE
        WHEN RIGHT(dense_rank, 1) = 1 AND dense_rank != 11 THEN CONCAT(dense_rank, 'st')
        WHEN RIGHT(dense_rank, 1) = 2 AND dense_rank != 12 THEN CONCAT(dense_rank, 'nd')
        WHEN RIGHT(dense_rank, 1) = 3 AND dense_rank != 13 THEN CONCAT(dense_rank, 'rd')
        ELSE CONCAT(dense_rank, 'th')
      END
  END AS position
FROM
  RankedAverages
ORDER BY
  unique_average DESC;

The query outputs the unique averages for students along with their corresponding positions based on the specified criteria. It calculates the average for each student, assigns a dense rank based on the descending order of averages, and then determines the position for each unique average.
While this is fine, it’s not there yet because I also want to assign ranks based on average ranges.

For better understanding, please see the imageranking
This logic is calculated from the 4th position.

In the image, from the 4th position, we have 91.80, while 5th position is 91.10.
Since they are in the same range, i.e 91 range, they both should be in the 4th position.
91.80 – 4th
91.10 – 4th
89.20 – 5th
86.90 – 6th
86.80 – 6th
86.00 – 6th ….

I would appreciate any guidance on how to achieve this

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Figured it out with 98% help from @TSCAmerica.com

    WITH RankedAverages AS (
    SELECT
        student_id,
        CAST(AVG(ft_tot_score) AS DECIMAL(10, 2)) AS unique_average,
        TRUNCATE(AVG(ft_tot_score), 0) AS average_range
    FROM
        ftscores_primary
    WHERE
        class_id = 9 AND
        section_id = 3 AND
        session_id = 19
    GROUP BY
        student_id
),
RankedWithDenseRank AS (
    SELECT
        unique_average,
        DENSE_RANK() OVER (ORDER BY average_range DESC) AS dense_rank
    FROM
        RankedAverages
),
RankedWithPositions AS (
    SELECT
        unique_average,
        CASE
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 1 THEN 1  -- condition for 1st position
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 2 THEN 2  -- condition for 2nd position
            WHEN RANK() OVER (ORDER BY unique_average DESC) = 3 THEN 3  -- condition for 3rd position
            ELSE dense_rank + 1  -- starting from 4th position
        END AS position
    FROM
        RankedWithDenseRank
)

SELECT
    unique_average,
    position
FROM
    RankedWithPositions
ORDER BY
    position, unique_average DESC;

  2. 0

    I changed the SQL query to group students into average ranges by truncating their average scores to whole numbers and then assigns ranks based on these ranges using the DENSE_RANK() function. Try this –>

    WITH RankedAverages AS (
    SELECT
        student_id,
        CAST(AVG(ft_tot_score) AS DECIMAL(10, 2)) AS unique_average,
        TRUNCATE(AVG(ft_tot_score), 0) AS average_range,
        DENSE_RANK() OVER (ORDER BY CAST(AVG(ft_tot_score) AS DECIMAL(10, 2)) DESC) AS unique_rank
    FROM
        ftscores_primary
    WHERE
        class_id = 9 AND
        section_id = 3 AND
        session_id = 19
    GROUP BY
        student_id
),
RankedWithGroupedPositions AS (
    SELECT
        student_id,
        unique_average,
        average_range,
        CASE
            WHEN unique_rank <= 3 THEN unique_rank
            ELSE DENSE_RANK() OVER (ORDER BY average_range DESC) + 3
        END AS position
    FROM
        RankedAverages
)

SELECT
    student_id,
    unique_average,
    position
FROM
    RankedWithGroupedPositions
ORDER BY
    position, unique_average DESC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search