Group By and Running Total and Totals using SQL Query

Question

My sql query returns the following values. How do I

1. Group By 2. Add Running Total 3. Add Total

to get it done as shown in pictured example 1, 2 and 3 below?

I need 3 queries to get the following results:

1: put it in Group By to show as

2: Add a column to return Running Total as

3: Add a Total for each Group

Answer 1

Check the query for the third problem:

select *, 
    sum(Price) over (partition by Category, Unit order by SerialNo) as RunningTotal,
    case when lead(SerialNo) over (partition by Category, Unit order by SerialNo) is null 
    then cast(sum(Price) over (partition by Category, Unit) as varchar(max)) else '' end as TotalValue 
from MyTable
order by Category, Unit, SerialNo