Running Total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Running Total

Author

Topic

jaystar
Starting Member

12 Posts

Posted - 2012-07-09 : 06:45:21

Hi all I have spent somem time googling and trying various methods but I cant get the results I require mainly because I am new to sql.

I have Id,Category in my DB and would like to have a running total column.

Mechanical 12
Mecahnical 1
Mecahnical 14
Mechanical 2

I would like the results to be like so

Mechanical 12 12
Mechanical 1 13
Mechanical 14 27
Mechanical 2 29

These can be multiple entrys for a mechaincal fault each day.

I managed to put this together but the results make no sense to me

SELECT TOP (100) PERCENT a.Docket_Category, a.Duration, COUNT(b.Docket_Id) AS Running_Total
FROM dbo.DocketTB AS a INNER JOIN
dbo.DocketTB AS b ON a.Docket_Id >= b.Docket_Id
Where a.Docket_Category='Mechanical'
GROUP BY a.Docket_Category, a.Duration
ORDER BY a.Duration

Thanks

USE [SLADB]
GO

/****** Object: Table [dbo].[DocketTB] Script Date: 07/09/2012 11:44:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DocketTB](
[Docket_Id] [int] IDENTITY(1,1) NOT NULL,
[Docket_Number] [int] NULL,
[Docket_Machine] [nchar](10) NULL,
[Docket_Status] [nchar](10) NULL,
[Docket_EngineerName] [nchar](50) NULL,
[Docket_Category] [nchar](50) NULL,
[Docket_SubCategory] [nchar](50) NULL,
[Duration] [int] NULL,
[Module] [nchar](100) NULL,
[Section] [nchar](100) NULL,
[Waittime] [int] NULL,
[Operator_Name] [nchar](100) NULL,
[Monitor_Time] [int] NULL,
[spare8] [nchar](100) NULL,
[Docket_EngStart] [datetime] NULL,
[Docket_EngFinish] [datetime] NULL,
[Docket_DateRaised] [datetime] NULL,
[Docket_Date] [datetime] NULL,
[Con1] [nchar](100) NULL,
[Con2] [nchar](100) NULL,
[Con3] [nchar](100) NULL,
[Con4] [nchar](100) NULL,
[Con5] [int] NULL,
[Con6] [int] NULL,
[Con7] [int] NULL,
[Contract] [bit] NULL,
[Weekend] [bit] NULL,
[Con10] [bit] NULL,
CONSTRAINT [PK_DocketTB] PRIMARY KEY CLUSTERED
(
[Docket_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 07:31:16

To calculate a running total, you would need some way to order the rows. When you do a select from the table, even though the rows may appear to be ordered in some fashion, that ordering is not guaranteed - SQL knows nothing about ordering unless you specify what it should be.

Do you have a serial id or something similar that can be used to order the rows?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-09 : 07:34:43

You need to have some means of ordering the rows to have a chance of doing this.
Something like

;with cte as (select cat, val, seq = row_num() over (order by something) from tbl)
select *, (select sum(val) from cte c2 where c2.seq <= c1.seq and c.cat = c1.cat)
from cte c1
order by c1.cat, c1.seq

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 09:37:22

Hopefully you should have audit column like datecreated which should specify your order in calculating running total

see scenario 1

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources