how to count col1 and col2 as One, where col1 is not duplicate and col2 = fail

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  July 2, 2018 at 1:42 pm

  #1996063

  
SELECT Total_Input, Total_OK, Total_Fail,
     CASE WHEN Total_Fail > 0 AND Total_Fail > Total_OK THEN Total_OK
     WHEN Total_Fail > 0 THEN Total_Fail
     ELSE 0 END AS RETEST
FROM (
      SELECT
          count(distinct(Col1)) as Total_Input,
          sum(case when Col2='OK' then 1 else 0 end) as Total_OK,
          sum(case when Col2='FAIL' then 1 else 0 end) as Total_Fail
      FROM MyTableName
      where line_no='H5'and
          TEST_DTTM BETWEEN TO_DATE('20180528','yyyymmdd') AND TO_DATE('20180530 235959','yyyymmdd hh24miss')
          and Col1 LIKE 'aa%'
group by line_no
) AS derived

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".