I have data on which I need to count +1 if a particular condition exists or another condition doesn't exist. I'm using SQL Server 2008.

I shred the following simplified sample XML into a temp table and validate it:

  <product type="1">
    <param type="1">
      <item mode="0" weight="1" />
    </param>
    <param type="2">
      <item mode="1" weight="1" />
      <item mode="0" weight="0.1" />
    </param>
    <param type="3">
      <item mode="1" weight="0.75" />
      <item mode="1" weight="0.25" />
    </param>
  </product>

The validation in concern is the following rule:

For each product type, for each param type, mode may be 0 & (1 || 2). In other words, there may be a 0(s), but then 1s or 2s are required, or there may be only 1(s) or 2(s). There cannot be only 0s, and there cannot be 1s and 2s.

The only part I haven't figured out is how to detect if there are only 0s. This seems like a "not having" problem.

The validation code (for this part):

WITH t1 AS (
    SELECT      SUM(t.ParamWeight) AS S, COUNT(1) AS C, 
                t.ProductTypeID, t.ParamTypeID, t.Mode
    FROM        @t AS t
    GROUP BY    t.ProductTypeID, t.ParamTypeID, t.Mode
),
    ...
    UNION ALL           
    SELECT      TOP (1) 1   -- only mode 0 & (1 || 2) is allowed
    FROM        t1
    WHERE       t1.Mode IN (1, 2)
    GROUP BY    t1.ProductTypeID, t1.ParamTypeID
    HAVING      COUNT(1) > 1
    UNION ALL
    ...
)
SELECT      @C = COUNT(1)
FROM        t2

This will show if any mode 1s & 2s are mixed, but not if the group contains only a 0. I'm sure there is a simple solution, but it's evading me right now.

EDIT:

I thought of a "cheat" that works perfectly. I added the following to the above:

SELECT      TOP (1) 1   -- only mode 0 & (null || 1 || 2) is allowed
FROM        t1
GROUP BY        t1.ProductTypeID, t1.ParamTypeID
HAVING      SUM(t1.Mode) = 0

However, I'd still like to know how to do this without cheating.