Exclusive SUMs with CASE I'm looking for a way to make each SUM exclusive, i.e., it doesn't count one record more than once. You might think that with the way the date ranges are set up, it wouldn't count more than once, but in this particular database application the dates are very peculiar. TotalCount is correct, but I'm getting 2 additional records for Count1 and Count2. I have tried wrapping the SUMs in an outer CASE, but have not been able to get it to work. Any ideas?

select 
 Sum(case   --'Before Admission'
 when ((EvalCheckboxDate &lt; UnitArrivalTime) and (m_value&lt;&gt;'No' or m_value   is not null)) then 1 else 0 
 end) as Count0,

 Sum(case   --'Within 24 Hours'
 when ((EvalCheckboxDate &gt;= UnitArrivalTime and EvalCheckboxDate &lt;= dateadd(hh,24,UnitArrivalTime)) and (m_value&lt;&gt;'No' or m_value is not null)) then 1 else 0 
 end) as Count1,

 Sum(case  --'Between 24 and 48 Hours'
 when ((EvalCheckboxDate &gt; dateadd(hh,24,UnitArrivalTime) and EvalCheckboxDate &lt;= dateadd(hh,48,UnitArrivalTime)) and (m_value&lt;&gt;'No' or m_value is not null)) then 1 else 0
 end) as Count2,

 Sum(case   --'Greater than 48 Hours'
 when (EvalCheckboxDate &gt; dateadd(hh,48,UnitArrivalTime) and (m_value&lt;&gt;'No' or m_value is not null)) then 1 else 0
 end) as Count3,

 Sum(case   --'Recorded as Not Done'
 when m_value='No' then 1 else 0
 end) as Count4,

 Sum(case   --'Not Recorded'
 when m_value is null then 1 else 0
 end) as Count5,  
--
count(*) as TotalCount
from TFDataset
Here's one way to make them mutually exclusive:

select count(case X when 'A' then 1 end)
     , count(case X when 'B' then 1 end)
     , ...
from (    
  select 
    case   --'Before Admission'
    when ((EvalCheckboxDate &lt; UnitArrivalTime) 
     and (m_value&lt;&gt;'No' or m_value   is not null)) then 'A'
           --'Within 24 Hours'
    when ((EvalCheckboxDate &gt;= UnitArrivalTime 
     and EvalCheckboxDate &lt;= dateadd(hh,24,UnitArrivalTime)) 
     and (m_value&lt;&gt;'No' or m_value is not null)) then 'B'  
    when ...
    [...]
    end as X
  from TFDataset
) as T


I.e. the idea is to use one case stmt to classify each row and then count the amount of each one. Thye code will be a bit simpler if you can live with something like:

select X, count(1)
from (    
  select 
    case   --'Before Admission'
    when ((EvalCheckboxDate &lt; UnitArrivalTime) 
     and (m_value&lt;&gt;'No' or m_value   is not null)) then 'A'
           --'Within 24 Hours'
    when ((EvalCheckboxDate &gt;= UnitArrivalTime 
     and EvalCheckboxDate &lt;= dateadd(hh,24,UnitArrivalTime)) 
     and (m_value&lt;&gt;'No' or m_value is not null)) then 'B'  
    when ...
    [...]
    end as X
  from TFDataset
) as T
group by X


and then transpose the result in the application