SQL- GROUP BY with CASE-CodePudding

I have a query which is

Select FinalGradeStudent from grade

and it brings me the following column

FinalGradeStudent
5
2
5
2
2
0
2
4

n grades... and I want something similar to this, to see the number of students who passed and failed the subject

performance	Estudents
Passed	3
Failed	5

the query I have done is the following

SELECT CASE WHEN FinalGradeStudent >= 3 THEN 'Passed' ELSE 'Failed' END Performance, COUNT(*) Estudents 
from Grade GROUP BY FinalGradeStudent

but my result is the following

Performance	Estudents
Passed	2
Failed	4
Passed	1
Failed	1

CodePudding user response：

Your query is fine, but the CASE expression needs to appear in the GROUP BY clause:

SELECT
    CASE WHEN FinalGradeStudent >= 3 THEN 'Passed' ELSE 'Failed' END Performance,
    COUNT(*) Estudents
FROM Grade
GROUP BY
    CASE WHEN FinalGradeStudent >= 3 THEN 'Passed' ELSE 'Failed' END;

CodePudding user response：

You can simply first create a temporary table with case and then do a group by query on that.

CREATE TABLE st (
  marks int
);

insert into st values(5);
insert into st values(2);
insert into st values(5);
insert into st values(2);
insert into st values(2);
insert into st values(0);
insert into st values(2);
insert into st values(4);
insert into st values(3);

-- QUERY
select sta.status as performance, count(*) as Estudents from (
  select case
    when marks>3 then 'PASSED'
    else 'FAILED'
    end as status
  from st) sta
group by sta.status;