Question: from the following table in SQL Server, how do I write a query to display Name, Class and total number of students who have secured more than 450 marks, class wise?
The following query just displays name and class of students who have secured more then 450 marks. But query needs to display the total number of such students, as well. GROUP BY does not work since, as expected, it will just display 1 record per such student:
SELECT Name, Class,
FROM MyTable
WHERE Marks > 450
| RollNo | Name | Class | Gender | City | Marks |
|---|---|---|---|---|---|
| 1 | Na Ida | X | M | Agra | 551 |
| 2 | Saurabh | YO | M | Mumbai | 462 |
| 3 | Sana | Xl | F | Delhi | 400 |
| 4 | Trsla | kli | F | Mumbai | 450 |
| 5 | Store | Xll | M | Delhi | 369 |
| 6 | Mar isla | Xl | F | Dubai | 250 |
| 7 | Neha | x | F | Moscow | 377 |
| 8 | Nishant | x | M | Moscow | 489 |
1
you can write your SQL query as follows:
SELECT
Name,
Class,
(SELECT COUNT(*)
FROM MyTable
WHERE Marks > 450 AND Class = t.Class) AS TotalStudents
FROM
MyTable t
WHERE
Marks > 450;
The main query retrieves the Name and Class of the students who have secured more than 450 marks, while the subquery ‘(SELECT COUNT(*) FROM MyTable WHERE Marks > 450 AND Class = t.Class)’ provides the total count of such students for each class. ‘Class = t.Class’ within subquery helps to perform the count class wise.
you can check this site for crafting subquries in sql: https://mode.com/sql-tutorial/sql-sub-queries
nabin sademba is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.