SQL- parse data

I am creating a Test Automation ‘Health Page’

On this page I would like to display a 7 day trend of how many tests ended with status PASS/FAIL/UNDEFINED. I am doing it in Oracle Application Express. I would like the graph to look something like this:

However I do not know how to separate the results into days. Here is my count query:

select  
                sum (case when STATES.NAME='Pass'then 1 else 0 end) AS PASS,
                sum (case when STATES.NAME='Fail'then 1 else 0 end) AS FAIL, 
                sum (case when STATES.NAME='Undefined'then 1 else 0 end) AS Undefined
          
from TEST_CASE TEST
INNER JOIN TEST_CASE_TEST_RUN  TESTRUNCASE on TEST.TEST_CASE_ID=TESTRUNCASE.TEST_CASE_ID
INNER JOIN TEST_RUN RUN on TESTRUNCASE.TEST_RUN_ID=RUN.TEST_RUN_ID
INNER JOIN EXECUTION EXE on TESTRUNCASE.TEST_CASE_IN_TEST_RUN_ID=EXE.TEST_CASE_IN_TEST_RUN_ID 
LEFT JOIN TEST_STEP_STATE STATES  on STATES.TEST_STEP_RESULT_STATE_ID=EXE.TEST_STEP_RESULT_STATE_ID
where  actual_Start_date > sysdate -7
;

This is the result of the query:

How can I change the query to give me a result for DAY 1, DAY 2, DAY3 etc(Im ok if it also shows shortened version of date in format ‘DD-MM’)?

to_char(EXE.ACTUAL_END_DATE,’DD-MM’) as DAY ,

Answer

You can use GROUP BY and to_char(actual_Start_date ,'d')which would return 1 – 7

select  
     to_char(actual_Start_date ,'d')  as days,
                sum (case when STATES.NAME='Pass'then 1 else 0 end) AS PASS,
                sum (case when STATES.NAME='Fail'then 1 else 0 end) AS FAIL, 
                sum (case when STATES.NAME='Undefined'then 1 else 0 end) AS Undefined
          
from TEST_CASE TEST
INNER JOIN TEST_CASE_TEST_RUN  TESTRUNCASE on TEST.TEST_CASE_ID=TESTRUNCASE.TEST_CASE_ID
INNER JOIN TEST_RUN RUN on TESTRUNCASE.TEST_RUN_ID=RUN.TEST_RUN_ID
INNER JOIN EXECUTION EXE on TESTRUNCASE.TEST_CASE_IN_TEST_RUN_ID=EXE.TEST_CASE_IN_TEST_RUN_ID 
LEFT JOIN TEST_STEP_STATE STATES  on STATES.TEST_STEP_RESULT_STATE_ID=EXE.TEST_STEP_RESULT_STATE_ID
where  actual_Start_date > sysdate -7
GROUP BY 1;