SQL CASE AND DISTINCT-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You might use GROUP BY with SUM:

SELECT PRSNO, IF(SUM(IF(STATUS = 'Ontime', 1, -1)) >= 0, 'Ontime', 'Delay') STATUS
FROM prsystem GROUP BY 1;