restrict duplicate rows in specific columns in mysql
- by JPro
I have a query like this :
select testset,
count(distinct results.TestCase) as runs,
Sum(case when Verdict = "PASS" then 1 else 0 end) as pass,
Sum(case when Verdict <> "PASS" then 1 else 0 end) as fails,
Sum(case when latest_issue <> "NULL" then 1 else 0 end) as issues,
Sum(case when latest_issue <> "NULL" and issue_type = "TC" then 1 else 0 end) as TC_issues
from results
join testcases on results.TestCase = testcases.TestCase
where platform = "T1_PLATFORM" AND testcases.CaseType = "M2"
and testcases.dummy <> "flag_1"
group by testset
order by results.TestCase
The result set I get is :
testset runs pass fails issues TC_issues
T1 66 125 73 38 33
T2 18 19 16 16 15
T3 57 58 55 55 29
T4 52 43 12 0 0
T5 193 223 265 130 22
T6 23 12 11 0 0
My problem is, this is a result table which has testcases running multiple times. So,
I am able to restrict the runs using the distinct TestCases but when I want the pass and fails, since I am using case I am unable to eliminate the duplicates.
Is there any way to achieve what I want?
any help please?
thanks.
