Aggregate Sum - 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

Aggregate Sum

Author

Topic

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2011-12-22 : 11:03:40

Hi,

I have this table:

ID type qty
1 Purchase 10
2 Purchase 20
3 Sale 8
4 Purchase 15
5 Sale 2
6 Sale 8

I want to create a view that would aggegate sum the qty on another column. Such as this:

ID type qty Aggregate
1 Purchase 10 10
2 Purchase 20 30
3 Sale 8 38
4 Purchase 15 53
5 Sale 2 55
6 Sale 8 63

any help is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 11:07:48

[code]
SELECT t.ID,
t.type,
t.qty,
t1.Aggregate
FROM Table t
CROSS APPLY (SELECT SUM(Qty) AS [Aggregate]
FROM Table
WHERE ID < = t.ID
)t1
[/code]

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-22 : 11:09:25

Doing running totals is one of those things that SQL is not very good at. Here is an alternate approach.


;WITH cte AS
(
	SELECT *,qty AS RunningTotal FROM YourTable
	
	UNION ALL
	
	SELECT t.*,c.RunningTotal+t.qty
	FROM YourTable t INNER JOIN cte c ON c.ID+1 = t.ID
)
SELECT * FROM cte OPTION (MAXRECURSION 0);

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 11:21:24

[code]

CREATE TABLE #myTable99([ID] int, [type] varchar(20), [qty] int)
GO

INSERT INTO #myTable99([ID], [type], [qty])
SELECT 1, 'Purchase', 10 UNION ALL
SELECT 2, 'Purchase', 20 UNION ALL
SELECT 3, 'Sale', 8 UNION ALL
SELECT 4, 'Purchase', 15 UNION ALL
SELECT 5, 'Sale', 2 UNION ALL
SELECT 6, 'Sale', 8
GO

SELECT * FROM #myTable99
GO
SELECT t.[ID], t.[qty], t.[type], xxx.[SUM_qty]
FROM (
SELECT l.[ID]
, SUM(r.[qty]) AS [SUM_qty]
FROM #myTable99 l
INNER JOIN #myTable99 r
ON r.[ID] <= l.[ID]
GROUP BY l.[ID]) AS XXX
INNER JOIN #myTable99 t
ON xxx.[ID] = t.[ID]
ORDER BY t.[id]
GO
DROP TABLE #myTable99
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 11:25:05

Sunita,

...it doesn't seem right

;WITH cte AS
(
SELECT *,qty AS RunningTotal FROM #myTable99

UNION ALL

SELECT t.*,c.RunningTotal+t.qty
FROM #myTable99 t INNER JOIN cte c ON c.ID+1 = t.ID
)
SELECT * FROM cte OPTION (MAXRECURSION 0);
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-22 : 11:34:40

Ugh! You are right, Brett. I promise, I will never post again without testing :-) .


CREATE TABLE #myTable99
(id INT, qty INT);

INSERT INTO #myTable99 VALUES 
(1,10),(2,20),(3,8),(4,15),(5,2),(6,8);


;WITH cte AS
(
SELECT *,qty AS RunningTotal FROM #myTable99 WHERE ID=1

UNION ALL

SELECT t.*,c.RunningTotal+t.qty
FROM #myTable99 t INNER JOIN cte c ON c.ID+1 = t.ID
)
SELECT * FROM cte OPTION (MAXRECURSION 0); 
GO

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 11:34:49

this is a little closer Sunita


;WITH cte AS
(
	SELECT *,qty AS RunningTotal FROM #myTable99
	
	UNION ALL
	
	SELECT t.*,c.RunningTotal+t.qty
	FROM #myTable99 t INNER JOIN cte c ON c.ID+1  <= t.ID
)
SELECT * FROM cte  ORDER BY [ID] OPTION (MAXRECURSION 0);  
GO

But you list the sums for everything before, not just the one before

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 11:38:06

yeah that works now...now if I just knew what the OPTION is...never seen that before

is that a 2k8R2 compatibility?

EDIT: Duh...never seen it used before with recursion

quote:

MAXRECURSION number
Specifies the maximum number of recursions allowed for this query. number is a non-negative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources