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
>Solution :
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 |