restrict duplicate rows in specific columns in mysql

Question

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.

UPDATE :

TestCases Table : 

ID TestCase CaseType dummy
1  101      M1       flag_0
2  102      M2       flag_1


Results table :

ID TestCase TestSet Verdict latest_issue Platform 
1  101      T1      PASS    NONE         T1_PLATFORM
2  102      T2      FAIL    YES          T2_PLATFORM
3  101      T1      FAIL    YES          T1_PLATFORM

Answer 1

I believe you are after something like this:

select
  testset,
  count(*) as runs,
  sum(passed) as pass,
  count(*) - sum(passed) as fails,
  sum(issues) as issues,
  sum(tc_issues) as tc_issues
from
  ( select 
      r.testset, 
      r.testcase, 
      max(if(r.verdict = 'PASS', 1, 0)) as passed
      max(if(r.latest_issue <> 'NULL', 1, 0)) as issues,
      max(if(r.latest_issue <> 'NULL' and issue_type = 'TC', 1, 0)) as tc_issues,
    from results r
      inner join testcases t on (r.testcase = t.testcase)
    where 
      r.platform = 'T1_PLATFORM' 
      and t.casetype = 'M2'
      and t.dummy <> 'flag_1'
    group by 
      r.testset, 
      r.testcase
  ) as t
group by
  testset
order by 1

Or, if you prefer, a version that uses count rather than sum (which should be a bit more efficient):

select
  testset,
  count(*) as runs,
  count(passed) as pass,
  count(*) - count(passed) as fails,
  count(issues) as issues,
  count(tc_issues) as tc_issues
from
  ( select 
      r.testset, 
      r.testcase, 
      max(if(r.verdict = 'PASS', 1, NULL)) as passed
      max(if(r.latest_issue <> 'NULL', 1, NULL)) as issues,
      max(if(r.latest_issue <> 'NULL' and issue_type = 'TC', 1, NULL)) as tc_issues,
    from results r
      inner join testcases t on (r.testcase = t.testcase)
    where 
      r.platform = 'T1_PLATFORM' 
      and t.casetype = 'M2'
      and t.dummy <> 'flag_1'
    group by 
      r.testset, 
      r.testcase
  ) as t
group by
  testset
order by 1

Note: I've used the mysql if function since it is more terse than the case syntax. If you prefer it the other way around, it should still work.