February 6, 2015 at 10:36 am
I want to show cumulative numbers, but don't know how to calculate them.
Here is an example of the source and the wanted result:
Source:
[Week] [Count]
1 15
2 5
3 6
4 10
(until 52)
Result:
[Week] [Count]
1 15
2 20
3 26
4 36
Is this possible, and how?
TestData:
USE TestDb /*SqlServer 2005*/
CREATE TABLE Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO Test ([Week], [Count]) VALUES (1, 15)
INSERT INTO Test ([Week], [Count]) VALUES (2, 5)
INSERT INTO Test ([Week], [Count]) VALUES (3, 6)
INSERT INTO Test ([Week], [Count]) VALUES (4, 10)
February 6, 2015 at 10:48 am
This sounds like a running total problem. In 2012 this is easier using LEAD and LAG but in 2008 those aren't yet available. Check out this article from Jeff Moden which goes into depth of one way to deal with this type of issue.
http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2015 at 12:49 pm
Further on Sean's answer, here is the "hard way" (don't worry, the server does the work:-D) of doing a running total on SQL Server 2008 and earlier
😎
USE tempdb;
GO
IF OBJECT_ID(N'dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
/*SqlServer 2005*/
CREATE TABLE dbo.Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO dbo.Test ([Week], [Count]) VALUES (1, 15)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (2, 5)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (3, 6)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (4, 10)
SELECT
T.Week
,T.Count
,(SELECT SUM(X.Count)
FROM dbo.Test X
WHERE T.Week >= X.Week) AS RT_COUNT
FROM dbo.Test T;
Results
Week Count RT_COUNT
----------- ----------- ---------
1 15 15
2 5 20
3 6 26
4 10 36
February 6, 2015 at 12:59 pm
Eirikur Eiriksson (2/6/2015)
Further on Sean's answer, here is the "hard way" (don't worry, the server does the work:-D) of doing a running total on SQL Server 2008 and earlier😎
USE tempdb;GO
IF OBJECT_ID(N'dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
/*SqlServer 2005*/
CREATE TABLE dbo.Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO dbo.Test ([Week], [Count]) VALUES (1, 15)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (2, 5)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (3, 6)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (4, 10)
SELECT
T.Week
,T.Count
,(SELECT SUM(X.Count)
FROM dbo.Test X
WHERE T.Week >= X.Week) AS RT_COUNT
FROM dbo.Test T;
Results
Week Count RT_COUNT----------- ----------- ---------
1 15 15
2 5 20
3 6 26
4 10 36
Careful here! I would not recommend using this approach. This is a triangular join and is basically a form of RBAR. Please see the article I referenced above. Jeff goes into detail about this type of running total and how awful the performance of this is with just a few rows.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2015 at 1:33 pm
Sean Lange (2/6/2015)
Eirikur Eiriksson (2/6/2015)
Further on Sean's answer, here is the "hard way" (don't worry, the server does the work:-D) of doing a running total on SQL Server 2008 and earlier😎
USE tempdb;GO
IF OBJECT_ID(N'dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
/*SqlServer 2005*/
CREATE TABLE dbo.Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO dbo.Test ([Week], [Count]) VALUES (1, 15)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (2, 5)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (3, 6)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (4, 10)
SELECT
T.Week
,T.Count
,(SELECT SUM(X.Count)
FROM dbo.Test X
WHERE T.Week >= X.Week) AS RT_COUNT
FROM dbo.Test T;
Results
Week Count RT_COUNT----------- ----------- ---------
1 15 15
2 5 20
3 6 26
4 10 36
Careful here! I would not recommend using this approach. This is a triangular join and is basically a form of RBAR. Please see the article I referenced above. Jeff goes into detail about this type of running total and how awful the performance of this is with just a few rows.
Hence the "hard way" note, this is in fact an O(N^2) approach, didn't mean to recommend it, posted it rather as a warning. Works "wonders" on very small sets though
😎
February 6, 2015 at 1:34 pm
Eirikur and Sean, thank you.
I have SqlServer 2012 on my developper machine, but the production machine is 2005 :crying:.
Now I gona try to fit my query into the model you showed. (the test table I provided, is in reality the result of a complex query).
With your examples I must succeed. 😎
February 7, 2015 at 2:32 am
For completeness, a SQL Server 2012 and later running total using a window function method
😎
USE tempdb;
GO
IF OBJECT_ID(N'dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
/*SqlServer 2012+*/
CREATE TABLE dbo.Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO dbo.Test ([Week], [Count])
VALUES (1, 15)
,(2, 5)
,(3, 6)
,(4, 10);
;
SELECT
T.Week
,T.Count
,SUM(T.Count) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY T.Week
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS RT_COUNT
FROM dbo.Test T;
Results
Week Count RT_COUNT
----------- ----------- ---------
1 15 15
2 5 20
3 6 26
4 10 36
February 7, 2015 at 8:39 pm
Eirikur Eiriksson (2/6/2015)
Further on Sean's answer, here is the "hard way" (don't worry, the server does the work:-D) of doing a running total on SQL Server 2008 and earlier😎
USE tempdb;GO
IF OBJECT_ID(N'dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
/*SqlServer 2005*/
CREATE TABLE dbo.Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO dbo.Test ([Week], [Count]) VALUES (1, 15)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (2, 5)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (3, 6)
INSERT INTO dbo.Test ([Week], [Count]) VALUES (4, 10)
SELECT
T.Week
,T.Count
,(SELECT SUM(X.Count)
FROM dbo.Test X
WHERE T.Week >= X.Week) AS RT_COUNT
FROM dbo.Test T;
Results
Week Count RT_COUNT----------- ----------- ---------
1 15 15
2 5 20
3 6 26
4 10 36
Please... don't post that method even with a "hard way" warning.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2015 at 8:40 pm
Henk Schreij (2/6/2015)
Eirikur and Sean, thank you.I have SqlServer 2012 on my developper machine, but the production machine is 2005 :crying:.
Now I gona try to fit my query into the model you showed. (the test table I provided, is in reality the result of a complex query).
With your examples I must succeed. 😎
Drive the result to a temp table, snap the correct clustered index on it, and then use the Quirky Update correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply