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