title: CASE statement with Subquery in SQL Server
body: <p>Please see the sql query i have.</p>
  
  <pre><code>select 
  CASE 
      WHEN (SELECT COUNT(COL1) FROM FACT_ACTIVITY group BY FACT_ACTIVITY.COL2)&gt;1
              THEN (SELECT COUNT(COL3) FROM FACT_ACTIVITY) 
      WHEN (SELECT COUNT(COL1) FROM FACT_ACTIVITY group BY FACT_ACTIVITY.COL2)=1
              THEN (SELECT COUNT(COL4) FROM FACT_ACTTIVITY)
              END
  ,EXTRACT(year from fa.INC_START_DATETIME) AS YearOFF
  ,COUNT(fa.COL2) AS OffCount from FACT_ACTIVITY fa INNER JOIN
  DIM_OFF_TYPE do ON fa.OFF_TYPE_ID = do.OFF_TYPE_ID
  WHERE fa.OFF_TYPE_ID IN (24001) and YEAR(fa.INC_START_DATETIME) IN (2016,2017)
  group by do.OFF_TYPE_DESCR,YEAR(fa.INC_START_DATETIME)
  </code></pre>
  
  <p>The subquery in case WHEN gives lots of rows,but i want the subquery do the check for each FACT_ACTIVITY_ID and see if the count(col2) >1  and do the THEN statement.</p>