Write SQL Query to get Student Name and number of Students in same grade.

Given Student Table
ID | Name | Grade
1 | George | 1
2 | Smith | 2

Answer: We can use WITH clause for this problem. We first get the number students in each grade by using GROUP BY on grade. Then we use Sub-Query returned by WITH clause in Main query.

Query will be as follows:

WITH grade_count AS (
SELECT grade, COUNT(*) AS grade_count
FROM student
GROUP BY grade)
SELECT s.name AS student_name,
gc.grade_count AS grade_count
FROM student s,
grade_count gc
WHERE e.grade = gc.grade;