getting count and group by in PSQL using Case Or RoundOff and pivot based on a point? how many pupils have passed and how many have failed

Question

A pupil can pass or fail in test. Total Marks = 100. 0-30 marks means fail. 31-100 marks means passed exam.

Example: I have table called pupil:

    CREATE TABLE pupil
    (id integer primary key,
     marks integer,
     maxmarks integer)

Now insert some values like

insert into pupil (id, marks, maxmarks) values (1,11,100),(2,21,100),(3, 60,100);

Now the table looks like this:

id | marks | maxmarks 
----+-------+----------
  1 |    11 |      100
  2 |    21 |      100
  3 |    60 |      100

I want to see how many have got between 0-30 and how many have got between 31-100 marks

So I try to think of using group by and then getting count.

I wrote this query:

    select marks, count(*) from pupil group by marks;

which returns me this result:

 marks | count 
-------+-------
    60 |     1
    11 |     1
    21 |     1

But I don't want that result, I am expecting this result.

 marks | count 
-------+-------
 0-30  |     2
30-100 |     1

0-30 can be just 0 or 30 also, I don't care, I am interested in count column mostly. I tried various group by and case clauses and also heard about something called "pivot", but I am not sure how to do this?

Answer 1

The most simple approach would be using a conditional GROUP BY:

demos:db<>fiddle

SELECT
    CASE 
        WHEN marks <= 30 THEN 
        '0-30'
        ELSE '31-100'
    END,
    COUNT(*)
FROM 
    pupil
GROUP BY (marks <= 30)

Alternative approach could be the usage of a UNION clause:

SELECT
    '0-30',
    COUNT(*)
FROM 
    pupil
WHERE marks <= 30

UNION

SELECT
    '31-100',
    COUNT(*)
FROM 
    pupil
WHERE marks > 30