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 ,
Advertisement
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;