Conditionally counting

Consider the following RowSet:

@a  = 
    SELECT * FROM 
        (VALUES
            ("Contoso",    1500.0),
            ("Woodgrove",  2700.0),
            ((string)null, 6700.0)
        ) AS D( customer, amount );

We can easily count the number of rows:

@b =
    SELECT 
       COUNT() AS Count1
       FROM @a;

But what if we wanted to count the rows only if they met a certain criteria? We can accomplish this my using the SUM operator with an expression that will return either a 1 or 0.

@b =
    SELECT 
       COUNT() AS Count,
       SUM(customer.Contains("o") ? 1 : 0) AS CountContainsLowercaseO,
       SUM(customer==null ? 1: 0) AS CountIsNull
    FROM @a;
Count CountContainsLowercaseOnt2 CountIsNull
3 2 1

results matching ""

    No results matching ""