Always select a row sql

Question

I have a case statment (see below) but I always want to return a value even if nothing matches the WHERE clase. Is that possible? Thanks

SELECT 
    CASE 
        WHEN COUNT(*) > 10 THEN 1
        ELSE 0
    END
FROM 
fldt_QueryDSLAM lastDay
WHERE AccountID = @acc
    AND lastDay.DSL = @dsl
GROUP BY lastDay.DSL

score 2 · Answer 1 · edited May 23 '17 at 11:47

If the WHERE clause returns one row (or one group more precisely), remove the GROUP BY. Then you'll always have one row in the output.

I assume it will (return one group) because you have GROUP BY on a filter column, and AccountID isn't mentioned in either SELECT or GROOUP BY

SELECT 
    CASE 
        WHEN COUNT(*) > 10 THEN 1
        ELSE 0
    END
FROM 
    fldt_QueryDSLAM lastDay
WHERE
    AccountID = @acc AND lastDay.DSL = @dsl

For why, see this: Does COUNT(*) always return a result?

score 1 · Accepted Answer · answered Aug 09 '11 at 10:16

You can wrap the select within an isnull;

SELECT ISNULL((
    SELECT 
        CASE 
            WHEN COUNT(*) > 10 THEN 1
            ELSE 0
        END
    FROM 
    fldt_QueryDSLAM lastDay
    WHERE AccountID = @acc
        AND lastDay.DSL = @dsl
    GROUP BY lastDay.DSL
), -99 /*no match*/)

Always select a row sql

2 Answers2