How to summarize a set of rows and set a boolean based on a primary key and get a result in SQL

I have 3 rows having same key, I need to return as 'NO' if it doesnt contain the same value for all the rows having same key.

In this below table, The bool value for No 999 shoud be "No" as the Val for this records having the No 999 is same. The bool should be "Yes" if the value for that same No key has different value.
For 538 the val has 0,30,0 so the bool is "Yes"
For 422 the val has 30,30,30 so the bool is "No"

Solution i tried:

SELECT [No], [Name],Mat, val, 
       CASE WHEN COUNT(*) OVER (PARTITION BY No) =
            COUNT(Mat) OVER (PARTITION BY No) 
            AND MAX(val) OVER (PARTITION BY No) = 
                MIN(val) OVER (PARTITION BY No)
            THEN 'No'
	        WHEN COUNT(Mat) OVER (PARTITION BY No) > 0
            THEN 'Yes'     
       END AS Bool
FROM personal_table

Getting desired result if i use the below query but i am not sure the logic i am intended to use is correct or not

	SELECT [No], [Name],Mat, val, 
       CASE WHEN  MAX(val) OVER (PARTITION BY No) = 
                MIN(val) OVER (PARTITION BY No)
            THEN 'No'
	        WHEN COUNT(Mat) OVER (PARTITION BY No) > 0
            THEN 'Yes'     
       END AS Bool
  FROM personal_table

Thank You

Just compare the MIN with the MAX. You need to take into account nulls.

You can use the new IS DISTINCT FROM for a nullable compare

SELECT
  [No],
  Name,
  Mat,
  val, 
  CASE WHEN
    MAX(val) OVER (PARTITION BY No) IS DISTINCT FROM
    MIN(val) OVER (PARTITION BY No)
    THEN 'Yes'
    ELSE 'No'
  END AS Bool
FROM personal_table;

Or on older versions use

  CASE WHEN
    ISNULL(MAX(val) OVER (PARTITION BY No), 0) =
    ISNULL(MIN(val) OVER (PARTITION BY No), 0)

I'd do it with a subjoin against the aggregated set:

;WITH cte AS (
	SELECT	*
	FROM	(
		VALUES	(999, N'AURO', N'NULL', 0, N'Yes')
		,	(999, N'AURO', N'49790', 0, N'Yes')
		,	(999, N'AURO', N'53383', 0, N'Yes')
		,	(422, N'BIO', N'87103', 30, N'No')
		,	(422, N'BIO', N'87047', 30, N'No')
		,	(422, N'BIO', N'87100', 30, N'No')
		,	(538, N'NOVA', N'57819', 0, N'Yes')
		,	(538, N'NOVA', N'57850', 30, N'Yes')
		,	(538, N'NOVA', N'57788', 0, N'Yes')
	) t (No,Name,Mat,Val,Bool)
	)
SELECT	c.NO, c.Name, c.Mat, c.Val
,	CASE WHEN valcount = 1 THEN 'No' ELSE 'Yes' END
FROM	cte c
INNER JOIN (
	SELECT	count(DISTINCT val) AS valCount
	,	NO
	FROM	cte c2
	GROUP BY no
	) x
	ON	x.NO = c.No

Basically, you calculate the aggregate parts and then join it back to check if all values are distinct. Same can be achieved by a subquery:

;WITH cte AS (
	SELECT	*
	FROM	(
		VALUES	(999, N'AURO', N'NULL', 0, N'Yes')
		,	(999, N'AURO', N'49790', 0, N'Yes')
		,	(999, N'AURO', N'53383', 0, N'Yes')
		,	(422, N'BIO', N'87103', 30, N'No')
		,	(422, N'BIO', N'87047', 30, N'No')
		,	(422, N'BIO', N'87100', 30, N'No')
		,	(538, N'NOVA', N'57819', 0, N'Yes')
		,	(538, N'NOVA', N'57850', 30, N'Yes')
		,	(538, N'NOVA', N'57788', 0, N'Yes')
	) t (No,Name,Mat,Val,Bool)
	)
SELECT	c.NO, c.Name, c.Mat, c.Val
,	CASE WHEN (SELECT count(DISTINCT val) AS valCount FROM	cte c2 where c2.NO = c.NO) = 1 THEN 'No' ELSE 'Yes' END
FROM	cte c