How to know if all the cells have the same value in some column

How to know if all the cells have the same value in some column (title changed)

I want to 开发者_运维技巧have a bit scalar value that tells me if all the values in a column equal something:

DECLARE @bit bit
SELECT @bit = TRUEFORALL(Name IS NOT NULL) FROM Contact

UPDATE

I now realized that I actually don't need the TrueForAll, what I do need is to make sure, that all values in a column are equal, for example, I want to know whether all Group.Items have the same price.

Why not?

select count( distinct price) from table

If returns 1, all values are the same... Add

where price is not null

if need be

For your updated requirement something like this would appear to do what you want:

DECLARE @IsSameGroup bit
SELECT @IsSameGroup = CASE WHEN COUNT(*) > 1 THEN 0 ELSE 1 END
FROM (SELECT Name FROM Contact GROUP BY Name) groups

When the count is greater the 1 you have two different names (or prices depending on what you group on)

Not very good for NULLs, but 2008 can do:

SELECT 1 WHERE 'Blue' = ALL ( SELECT Color FROM dbo.Hat )

OR

DECLARE @bit bit

SET @bit = 
CASE ( SELECT 1 WHERE 'Blue' = ALL ( SELECT Color FROM dbo.Hat ))
WHEN 1 THEN 1 ELSE 0 END 

UPDATE

All same color

SET @bit = 
CASE(
   SELECT 1 WHERE
  (SELECT TOP(1) Color FROM dbo.Hat) = ALL ( SELECT Color FROM dbo.Hat )
    )
WHEN 1 THEN 1 ELSE 0 END 

Maybe this?

DECLARE @bit bit
if exists(SELECT Name FROM Contact WHERE Name IS NULL) 
   SET @bit = 0
ELSE
  SET @bit = 1

This solves your first question:

SELECT
    CASE
        WHEN EXISTS(
            SELECT 1
            FROM Contact
            WHERE Name IS NULL
        ) THEN 0
        ELSE 1
    END

ADDED:

This will solve your second:

SELECT
    CASE
        WHEN EXISTS(
            SELECT TOP 1 1 FROM (
                SELECT
                    ItemGroupName,
                    COUNT(Price) AS CNT
                FROM ItemGroup
                GROUP BY ItemGroupName
                HAVING COUNT(Price) > 1
            ) t
        ) THEN 0
        ELSE 1
    END

By the way, when you use the exists function, its better to SELECT 1 (a constant) so less data gets returned