Case statment based on a count

Question

I want to do a case statement based on the results of a count.

Something like:

SELECT
    CASE
        WHEN count(distinct column) = 1
            THEN Foo
        WHEN count(distinct column) = 2
            THEN foo2
    END,
    column2
FROM
    TABLE

The above throws errors such as:

Column 'column2' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

I've tried a few other variations like this, but to no avail.

Using MS SQL 2005.

Answer 1

Got this resolved yesterday. I was able to accomplish what I needed by joining in the table twice with different aliases, and using different join conditions for each. That allowed me to avoid needing to do the count because I had special-purpose tables that only had the correct values.

Sorry for the abstract code, but the result was similar to:

SELECT
    CASE
        WHEN table1.field IS NOT NULL
            THEN table1alias1.column1
        WHEN table2.field IS NOT NULL
            THEN table1alias2.column1
    END,
    column2
FROM
    TABLE0
    LEFT OUTER JOIN TABLE1 on TABLE0.id = TABLE1.field AND
        TABLE1.column1 LIKE '%foo%' as TABLE1ALIAS1
    LEFT OUTER JOIN TABL1 on TABLE0.id = TABLE1.field AND
        TABLE1.column1 LIKE '%fork%' as TABLE1ALIAS2

Answer 2

You have to GROUP BY column2:

SELECT
    CASE
        WHEN count(distinct column) = 1
            THEN Foo
        WHEN count(distinct column) = 2
            THEN foo2
    END,
    column2
FROM
    TABLE
GROUP BY Column2