SQL CASE AND DISTINCT

Question

I have a table like this

this is just a sample of my table

ID	PRSNO	QTY	DESC	STATUS
1	TEST1	1	Pen	Ontime
2	TEST1	1	Eraser	Delay
3	TEST1	1	Scissor	Ontime

ID	PRSNO	QTY	DESC	STATUS
4	TEST2	1	Pen	Delay
5	TEST2	1	Notebook	Delay
6	TEST2	1	Pentelpen	Delay

ID	PRSNO	QTY	DESC	STATUS
7	TEST3	1	Pen	Ontime
8	TEST3	1	Notebook	Ontime
9	TEST3	1	Pentelpen	Delay
9	TEST3	1	Pentelpen	Delay

ID	PRSNO	QTY	DESC	STATUS
7	TEST4	1	Keyboard	Delay
8	TEST4	1	Mouse	Ontime
9	TEST4	1	Monitor	Delay
9	TEST4	1	CPU	Delay

but my problem is i want to display this like this

PRSNO	STATUS
TEST1	Ontime
TEST2	Delay
TEST3	Ontime
TEST4	Delay

I just know how to distinct the PRSNO but i dont know how to calculate the status where if Ontime <= Delay
Ontime else Delay

My code is only for distinct

SELECT DISTINCT PRSNO FROM prsystem

Answer 1

You can try to use aggregate condition function compare count by Ontime and Delay from STATUS column

Query #1

SELECT PRSNO,
       CASE WHEN 
       COUNT(CASE WHEN STATUS = 'Ontime' THEN 1 END) >= 
            COUNT(CASE WHEN STATUS = 'Delay' THEN 1 END)  THEN 'Ontime' 
                ELSE 'Delay'  END STATUS
FROM prsystem
GROUP BY PRSNO;

PRSNO	STATUS
TEST1	Ontime
TEST2	Delay
TEST3	Ontime
TEST4	Delay

---

View on DB Fiddle