I want help in 2 queries : a)How many students have graduated with first class? b)How many students-CodePudding

The grading of the students based on the marks they have obtained is done as follows:

40-50  - Second class
50-60  - First Class
60-80  - First Class
80-100 - Distinctions.

The table Stud for the above query is given as:

ID|Name     |Marks                                                                                                                                                                           
11|Britney  | 95    
12|Dyana    | 55    
13|Jenny    | 66    
14|Christene| 88    
15|Meera    | 24    
16|Priya    | 76    
17|Priyanka | 77    
18|Paige    | 74    
19|Samantha | 87    
21|Julia    | 96    
27|Evil     | 79    
29|Jane     | 64    
31|Scarlet  | 80    
32|Kristeen |100    
34|Fanny    | 75    
37|Belvet   | 78
38|Danny    | 75

I've tried creating the grade table with assigning the grades first to the table with the following query:

Select from stud Grade=Case 
when marks>100 then 'Distinction' 
when 80>70 and marks<100 then 'Distinction' 
when marks>60 and marks<80 then 'First Class' 
when marks>50 and marks<60 then 'First Class' 
when marks>40 and marks<50 then 'Second Class' 
when marks<40 then 'Fail' 
else 'No Grade Available' end Grade ;

CodePudding user response：

Select count(*) res from stud where marks between 80 and 100

the result will have "res" with count of students who obtained marks between 80 and 100

CodePudding user response：

I would use a sum of counters that are at 1 exactly when the mark value matches the conditions to be assigned to the group in question, using a CASE WHEN expression:

WITH
-- your input, don't use in real query ..
indata(ID,Name,Marks) AS (
          SELECT 11,'Britney',95
UNION ALL SELECT 12,'Dyana',55
UNION ALL SELECT 13,'Jenny',66
UNION ALL SELECT 14,'Christene',88
UNION ALL SELECT 15,'Meera',24
UNION ALL SELECT 16,'Priya',76
UNION ALL SELECT 17,'Priyanka',77
UNION ALL SELECT 18,'Paige',74
UNION ALL SELECT 19,'Samantha',87
UNION ALL SELECT 21,'Julia',96
UNION ALL SELECT 27,'Evil',79
UNION ALL SELECT 29,'Jane',64
UNION ALL SELECT 31,'Scarlet',80
UNION ALL SELECT 32,'Kristeen',100
UNION ALL SELECT 34,'Fanny',75
UNION ALL SELECT 37,'Belvet',78
UNION ALL SELECT 38,'Danny',75
)
-- real query starts here
SELECT
  SUM(CASE WHEN marks > 50 AND marks <= 80 THEN 1 END) AS first_class_count
, SUM(CASE WHEN marks > 80                 THEN 1 END) AS distinction_count
FROM indata
-- out  first_class_count | distinction_count 
-- out ------------------- -------------------
-- out                 11 |                 5

CodePudding user response：

You can defined the categories in your select statement and use it in the group by to get a more easy to read result. Also, make sure to define the boundaries correctly. Here is an example

SELECT CASE WHEN marks BETWEEN 81 AND 100 then 'Distinction' 
            WHEN marks BETWEEN 51 AND 80 then 'First Class' 
            WHEN marks BETWEEN 40 AND 50 then 'Second class'  
            ELSE 'No Grade Available' 
        END Grade,
        COUNT(*) AS stud_count
FROM stud 
WHERE marks > 50   
GROUP BY CASE WHEN marks BETWEEN 81 AND 100 then 'Distinction' 
            WHEN marks BETWEEN 51 AND 80 then 'First Class' 
            WHEN marks BETWEEN 40 AND 50 then 'Second class'  
            ELSE 'No Grade Available' 
        END

SQL Fiddle