```
--------DATA in Cat_Tbl---------------------------
Cat1 Cat2 Value
a e1 113
b e1 14
a e2 15
c e3 13
a e1 13
a e2 11
c e4 1
```

PROBLEM:

For each distinct value in Cat1, user wants to find maximum of Values grouped by Cat2 and then sum them up.

Step 1: User wants to extract the following rows for example where Cat1 = ‘a’.

```
Cat1 Cat2 Value
a e1 113
a e2 15
a e1 13
a e2 11
```

Step 2: User wants to extract maximum corresponding to Cat1 and Cat2:

```
Cat1 Cat2 Value
a e1 113
a e2 15
```

Step 3: User wants to sum the Values for Cat1.

```
Cat1 SumOfValueColumn
a 128
b 14
c 14
```

SOLUTION 1 USING TEMPORARY TABLE #Max_Cat_Tbl

```
select Cat1, Cat2, max(Value) Maxvalue
into #Max_Cat_Tbl from Cat_Tbl
group by Cat1, Cat2 order by 1, 2
select Cat1, sum(Maxvalue)
from #Max_Cat_Tbl
group by Cat1
order by Cat1
drop table #Max_Cat_Tbl
go
```

SOLUTION 2 USING VIEW Max_Cat_Tbl_View

`create view Max_Cat_Tbl_View ( Cat1, Cat2, Maxvalue ) as select Cat1, Cat2, max(Value) from Cat_Tbl group by Cat1, Cat2 select Cat1, sum(Maxvalue) from Max_Cat_Tbl_View group by Cat1 order by Cat1 go`

Good SQL, good night.