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:
x
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;