Source:

```
CREATE TABLE #TempTab (Value INT, Value1 varchar(10), Value2 varchar(10),
GRP varchar(10))
INSERT INTO #TempTab
SELECT 1,'One','One','One'
UNION ALL
SELECT 1,'One','One','One'
UNION ALL
sELECT 1,'One','One','Two'
UNION ALL
SELECT 2,'One','One','One'
UNION ALL
SELECT 2,'One','One','Two'
UNION ALL
SELECT 2,'One','One','Three'
UNION ALL
SELECT 3,'One','One','One'
UNION ALL
SELECT 3,'One','One','One'
```

Current query effort:

```
SELECT Value, Value1, Value2, GRP
, COUNT(1) OVER(PARTITION BY Value, Value1, Value2) CNT
, ROW_NUMBER() OVER(PARTITION BY Value, Value1, Value2, GRP ORDER BY Value) RN
, CASE
WHEN COUNT(*) OVER (PARTITION BY Value, Value1, Value2, GRP) > 1 THEN 1
ELSE 0
END IsMultiple
FROM #TempTab
DROP TABLE #TempTab
```

Current output:

```
Value Value1 Value2 GRP CNT RN IsMultiple
1 One One One 3 1 1
1 One One One 3 2 1
1 One One Two 3 1 0
2 One One One 3 1 0
2 One One Two 3 1 0
2 One One Three 3 1 0
3 One One One 2 1 1
3 One One One 2 2 1
```

Desired output:

```
Value Value1 Value2 GRP CNT RN IsMultiple NoUniqueGRPed
1 One One One 3 1 1 2
1 One One One 3 2 1 2
1 One One Two 3 1 0 2
2 One One One 3 1 0 3
2 One One Two 3 1 0 3
2 One One Three 3 1 0 3
3 One One One 2 1 1 1
3 One One One 2 2 1 1
```

Goal:

I am trying to derive a field called NoUniqueGRPed. This field is basically count of unique grouped records based on Value, Value1, and Value2 fields. i.e. Value = 1, Value1 = One, and Value2 = One has three records but two unique GRP values (One and Two) so NoUniqueGRPed should be 2.

I'm having trouble trying to figure out how to do the unique aggregation/grouping.