Hi All
This post may seem repetitive to one of my previous questions.But I thought to post freshly. Following is the query:-
SELECT
JOB_NAME,
SUM(CASE Status WHEN 'Failure' THEN 1 ELSE 0 END) As Fail_Count,
COUNT(JOB_NAME) As OverallCount
FROM
COMP_HIS_TBL
GROUP BY
JOB_NAME
I need to add an additional field called "FailPercent" based on the formula :(Fail_Count/Overall_Count)*100 in the result.
What I have tried:
I tried the following queries but its throwing error:(
SELECT
JOB_NAME,
SUM(CASE Status WHEN 'Failure' THEN 1 ELSE 0 END) As Fail_Count,
COUNT(JOB_NAME) As OverallCount,
(Fail_Count/OverallCount)*100 as Fail_Percent
FROM
COMP_HIS_TBL
GROUP BY
JOB_NAME)
2)
Select JOB_NAME,T1.Fail_Percent from
(SELECT
JOB_NAME,
SUM(CASE Status WHEN 'Failure' THEN 1 ELSE 0 END) As Fail_Count,
COUNT(JOB_NAME) As OverallCount,
(Fail_Count/OverallCount)*100 as Fail_Percent
FROM
COMP_HIS_TBL
GROUP BY
JOB_NAME)as T1
3)
Select JOB_NAME,(Count(Fail_Count)/Count(OverallCount))*100 as Fail_percent,STATUS from COMP_HIS_TBL
where Fail_Count,OverallCount in (SELECT
JOB_NAME,
SUM(CASE Status WHEN 'Failure' THEN 1 ELSE 0 END) As Fail_Count,
COUNT(JOB_NAME) As OverallCount
FROM
COMP_HIS_TBL
GROUP BY
JOB_NAME)
Please provide a solution to this:(
Thank you