Advanced MySQL GROUP BY: Examples of Student Tuition and Grade Management Part 1

Want to become a MySQL expert? Our advanced GROUP BY tutorial can help you achieve that goal. Explore real-world scenarios of student tuition and grade management, and learn how to leverage GROUP BY to analyze and summarize large datasets with ease. Whether you’re a beginner or an experienced SQL user, this guide has something for everyone.

Student Management

Grouping by Gender

Suppose we have a table named students with columns id, name, gender, and date_of_birth. We want to find out the number of male and female students in our database. We can achieve this using the following query:

SELECT gender,
       COUNT(*) AS student_count
FROM students
GROUP BY gender;

In this query, we are selecting the gender column and using the COUNT() function to count the number of male and female students. We are grouping the data by the gender column using the GROUP BY clause.

Grouping by Age Range

Suppose we have a table named students with columns id, name, gender, and date_of_birth. We want to group the students into age ranges and find out how many students fall into each age range. We can achieve this using the following query:

SELECT CASE
           WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) < 18 THEN 'Under 18'
           WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 18 AND 24 THEN '18-24'
           WHEN TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE()) BETWEEN 25 AND 34 THEN '25-34'
           ELSE '35 and over'
       END AS age_range,
       COUNT(*) AS student_count
FROM students
GROUP BY age_range;

In this query, we are using the TIMESTAMPDIFF() function to calculate the age of each student. We are using the CASE statement to group the students into different age ranges. We are selecting the age_range column and using the COUNT() function to count the number of students in each age range. We are grouping the data by the age_range column using the GROUP BY clause.

Grouping by Class Year

Suppose we have a table named students with columns id, name, class_year, and major. We want to find out the number of students in each class year. We can achieve this using the following query:

SELECT class_year,
       COUNT(*) AS num_students
FROM students
GROUP BY class_year;

In this query, we are using the COUNT() function to count the number of students in each class year. We are selecting the class_year column and the num_students column. We are grouping the data by the class_year column using the GROUP BY clause.

Grouping by Major

Suppose we have a table named students with columns id, name, class_year, and major. We want to find out the number of students in each major. We can achieve this using the following query:

SELECT major,
       COUNT(*) AS num_students
FROM students
GROUP BY major;

In this query, we are using the COUNT() function to count the number of students in each major. We are selecting the major column and the num_students column. We are grouping the data by the major column using the GROUP BY clause.

Grouping by Class Year and Major

Suppose we have a table named students with columns id, name, class_year, and major. We want to find out the number of students in each class year and major combination. We can achieve this using the following query:

SELECT class_year,
       major,
       COUNT(*) AS num_students
FROM students
GROUP BY class_year,
         major;

In this query, we are using the COUNT() function to count the number of students in each class year and major combination. We are selecting the class_year, major, and num_students columns. We are grouping the data by the class_year and major columns using the GROUP BY clause.

Grouping by Class Year, Major, and Gender

Suppose we have a table named students with columns id, name, class_year, major, and gender. We want to find out the number of students in each class year, major, and gender combination. We can achieve this using the following query:

SELECT class_year,
       major,
       gender,
       COUNT(*) AS num_students
FROM students
GROUP BY class_year,
         major,
         gender;

In this query, we are using the COUNT() function to count the number of students in each class year, major, and gender combination. We are selecting the class_year, major, gender, and num_students columns.

Test Score Management

Grouping by Average Score

Suppose we have a table named test_scores with columns id, student_id, test_id, and score. We want to find out the average score for each test and for all tests combined. We can achieve this using the following query:

SELECT test_id,
       AVG(score) AS average_score
FROM test_scores
GROUP BY test_id WITH ROLLUP;

In this query, we are using the AVG() function to calculate the average score for each test. We are selecting the test_id column and the average_score column. We are grouping the data by the test_id column using the GROUP BY clause. We are also using the WITH ROLLUP clause to include an additional row that shows the average score for all tests combined.

Grouping by Student

Suppose we have a table named test_scores with columns id, student_id, test_id, and score. We want to find out the average score for each student across all tests. We can achieve this using the following query:

SELECT student_id,
       AVG(score) AS average_score
FROM test_scores
GROUP BY student_id;

In this query, we are using the AVG() function to calculate the average score for each student. We are selecting the student_id column and the average_score column. We are grouping the data by the student_id column using the GROUP BY clause.

Grouping by Test and Student

Suppose we have a table named test_scores with columns id, student_id, test_id, and score. We want to find out the average score for each test and student combination. We can achieve this using the following query:

SELECT test_id,
       student_id,
       AVG(score) AS average_score
FROM test_scores
GROUP BY test_id,
         student_id;

In this query, we are using the AVG() function to calculate the average score for each test and student combination. We are selecting the test_id, student_id, and average_score columns. We are grouping the data by the test_id and student_id columns using the GROUP BY clause.

Grouping by Test and Student

Suppose we have a table named test_scores with columns id, student_id, test_name, and score. We want to find out the highest score for each test and student combination. We can achieve this using the following query:

SELECT test_name,
       student_id,
       MAX(score) AS highest_score
FROM test_scores
GROUP BY test_name,
         student_id;

In this query, we are using the MAX() function to find the highest score for each test and student combination. We are selecting the test_name, student_id, and highest_score columns. We are grouping the data by the test_name and student_id columns using the GROUP BY clause.

View More: Advanced MySQL GROUP BY: Examples of Student Tuition and Grade Management Part 2