Group By and Running Total and Totals using SQL Query

Question

Group By and Running Total and Totals using SQL Query

Malam Malam 266

My sql query returns the following values. How do I

Group By 2. Add Running Total 3. Add Total

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

User's image

I need 3 queries to get the following results:

1: put it in Group By to show as

User's image

2: Add a column to return Running Total as

User's image

3: Add a Total for each Group

User's image

Accepted answer

0 additional answers

Your answer

Answer 1

Viorel 121.3K

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

Malam Malam 266 Reputation points

2025-04-16T13:39:36.85+00:00

I forgot to mention that the column "Price" is a varchar coming from a column that has XML data as dbo.xtag('Price', XmlData) Getting and error: Error converting data type varchar to numeric.

Share via

Group By and Running Total and Totals using SQL Query

0 additional answers

Your answer