running sum in a crosstab - 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

SQL Server 2000 Forums

SQL Server Development (2000)

running sum in a crosstab

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-18 : 09:53:05

Boulder writes "Would you please to tell me,
how to create a running sum within a crostab SQL query?
Like follows:

Date A B C Sum
----------------
1/1/01 1 0 3 4
2/1/01 5 1 0 10
3/1/01 2 0 0 12
4/1/01 7 0 0 19

Source is somewhat like:

Date Subj Number
------------------
1/1/01 A 1
1/1/01 C 3
2/1/01 A 5
2/1/01 B 1
... and so on.

What is the SQL code?
Thanks in advance."

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-18 : 14:49:35

a running sum like that? eww...

Only way I can possibly see that happening is a cursor

declare @sum int
set @sum = 0
declare the cursor
load cursor with date,a,b,c --will be an interesting select statement
@sum = @sum + (select a,b,c from cursor)
select date,a,b,c,@sum from cursor
union
loop cursor

man thats ugly... But I relly can't see any other way. I'm lacking query analyzer so its hard to write actual syntax for you... Hopefully the idea behind it is good enough. If not respond and I'll see what I can do

-----------------------
Take my advice, I dare ya

macka
Posting Yak Master

162 Posts

Posted - 2002-06-19 : 04:42:23

Have you looked at this ?

[url]http://www.sqlteam.com/item.asp?ItemID=2955[/url]

macka

ThreePea
Yak Posting Veteran

83 Posts

Posted - 2002-06-19 : 15:58:12

Are the values in the Subj column a known set of values? In other words, do you know that the values will always be 'A', 'B', 'C', ... 'X', or whatever? If so, the following piece of code should work. You can drop it directly in QA to test. Once you see how it works, substitute your table for temp table #t1, and make columns in #t2 that match all possible values.


CREATE TABLE #t1
(Date1 SMALLDATETIME
,Subject VARCHAR(1)
,Number INT)

CREATE TABLE #t2
(Date1 SMALLDATETIME
,ColA VARCHAR(5)
,ColB VARCHAR(5)
,ColC VARCHAR(5)
,RunBal INT DEFAULT 0)

DECLARE @RunBal INT

INSERT #t1 VALUES('1/1/01','A',1)
INSERT #t1 VALUES('1/1/01','C',3)
INSERT #t1 VALUES('2/1/01','A',10)
INSERT #t1 VALUES('2/1/01','B',1)

INSERT #t2 (Date1, ColA, ColB, ColC)
SELECT Date1, 
  SUM(CASE Subject WHEN 'A' THEN Number ELSE 0 END) AS 'A',
  SUM(CASE Subject WHEN 'B' THEN Number ELSE 0 END) AS 'B',
  SUM(CASE Subject WHEN 'C' THEN Number ELSE 0 END) AS 'C'
FROM #t1 
GROUP BY Date1
ORDER BY Date1

SET @RunBal = 0

UPDATE #t2
SET @RunBal = RunBal = (@RunBal + ColA + ColB + ColC)

SELECT *
FROM #t2

DROP TABLE #t1, #t2

========================================
Tolerance is the last virtue of an immoral society.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-06-20 : 09:36:48

This code will get the job done

CREATE TABLE Table1 (
Date DATETIME,
Subj Char(1),
Number INT)

Insert Table1 VALUES('1/1/01', 'A', 1 )
Insert Table1 VALUES('1/1/01', 'C', 3 )
Insert Table1 VALUES('2/1/01', 'A', 5 )
Insert Table1 VALUES('2/1/01', 'B', 1 )
Insert Table1 VALUES('3/1/01', 'A', 2 )
Insert Table1 VALUES('4/1/01', 'A', 7 )

SELECT A.Date,
SUM(CASE WHEN A.Subj = 'A' THEN A.Number ELSE 0 END) AS A,
SUM(CASE WHEN A.Subj = 'B' THEN A.Number ELSE 0 END) AS B,
SUM(CASE WHEN A.Subj = 'C' THEN A.Number ELSE 0 END) AS C,
(SELECT SUM(Number) FROM Table1 WHERE Date <= A.Date)
--select *
FROM Table1 A
GROUP BY A.Date

If you do not have a set number of Subj you will have to build it dynamically

DECLARE @SQL Varchar(8000)

SET @SQL = 'SELECT A.Date '

SELECT @SQL = @SQL + CHAR(13) + CHAR(9)+
', SUM(CASE WHEN A.Subj = ' + QUOTENAME(Subj,'''') + ' THEN A.Number ELSE 0 END) AS ' + QUOTENAME(Subj,'"')
FROM (SELECT DISTINCT Subj FROM Table1) AS A
ORDER BY Subj

SELECT @SQL = @SQL + CHAR(13) + CHAR(9) + ',(SELECT SUM(Number) FROM Table1 WHERE Date <= A.Date) AS "RuningTotal"
FROM Table1 A
GROUP BY A.Date'

SELECT @SQL
EXEC (@SQL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources