SUM INCREASING - 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 2005 Forums

Transact-SQL (2005)

SUM INCREASING

Author

Topic

agniwoni
Starting Member

28 Posts

Posted - 2008-10-01 : 09:25:10

Hi, the problem is that I have a set of data:
ITEM VOLUME
A 1
A 2
B 3
B 2
C 8
C 1

and I want to have SUM of VOLUME per ITEM but with additional column containing increasing sum. So the result looks like this:
ITEM SUM_VOL SUM_INCR
C 9 9
B 5 15
A 3 18

Without last column it is simple:
select ITEM, SUM(VOLUME)
group by ITEM
ORDER BY SUM(VOLUME) DESC

Please help!
Regards,
Agni

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 09:45:55

9 + 5 = 14. Not 15.
14 + 3 = 17. Not 18.

E 12°55'05.63"
N 56°04'39.26"

agniwoni
Starting Member

28 Posts

Posted - 2008-10-01 : 09:53:44

quote:
Originally posted by Peso

9 + 5 = 14. Not 15.
14 + 3 = 17. Not 18.

E 12°55'05.63"
N 56°04'39.26"

FOCUS TEST PASSED:
A

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 10:00:02

Not just a focus test.
It's also a test how interested you are in a solution; How much you want to learn and how much you want to be spoonfed.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-01 : 10:05:13

quote:
Originally posted by agniwoni

quote:
Originally posted by Peso

9 + 5 = 14. Not 15.
14 + 3 = 17. Not 18.

E 12°55'05.63"
N 56°04'39.26"

FOCUS TEST PASSED:
A

Judgement accepted

Madhivanan

Failing to plan is Planning to fail

agniwoni
Starting Member

28 Posts

Posted - 2008-10-01 : 10:14:28

quote:
Originally posted by Peso

Not just a focus test.
It's also a test how interested you are in a solution; How much you want to learn and how much you want to be spoonfed.

E 12°55'05.63"
N 56°04'39.26"

I just wonder: aren't You tired of writing all this?
Wouldn't it be better if You just post a solution
and in BTW section You could mention about my enormous misscalculation.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 10:18:11

What?
If I posted a suggestion to you and I had written some characters wrong, would you like that?

How can we know that you did a miscalculation?
How can we know there is a business rules when summing that you haven't told us of?

E 12°55'05.63"
N 56°04'39.26"

agniwoni
Starting Member

28 Posts

Posted - 2008-10-01 : 10:23:31

quote:
Originally posted by Peso

What?
If I posted a suggestion to you and I had written some characters wrong, would you like that?

How can we know that you did a miscalculation?
How can we know there is a business rules when summing that you haven't told us of?

E 12°55'05.63"
N 56°04'39.26"

Maybe because of Your outstanding analytical skills and... i assume a king of a sixt sense, because You did know that it was a misscalculation

GENIUS TEST PASSED:
A++

BTW Does this discussion take us anywhere?
Peace&love,
Agni

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 10:24:26

quote:
Originally posted by agniwoni

BTW Does this discussion take us anywhere?

Yes. We are one step closer to spoonfeeding.

DECLARE	@Sample TABLE
	(
		Item CHAR(1),
		Volume TINYINT
	)

INSERT	@Sample
SELECT	'A', 1 UNION ALL
SELECT	'A', 2 UNION ALL
SELECT	'B', 3 UNION ALL
SELECT	'B', 2 UNION ALL
SELECT	'C', 8 UNION ALL
SELECT	'C', 1

DECLARE	@Stage TABLE
	(
		RowID INT IDENTITY(1, 1),
		Item CHAR(1),
		Volume TINYINT
	)

INSERT		@Stage
		(
			Item,
			Volume
		)
SELECT		Item,
		SUM(Volume)
FROM		@Sample
GROUP BY	Item
ORDER BY	SUM(Volume) DESC

SELECT		s.Item,
		s.Volume,
		(SELECT SUM(x.Volume) FROM @Stage AS x WHERE x.RowID >= s.RowID) AS Incr
FROM		@Stage AS s

E 12°55'05.63"
N 56°04'39.26"

agniwoni
Starting Member

28 Posts

Posted - 2008-10-01 : 10:31:22

quote:

Originally posted by Peso

quote:
Originally posted by agniwoni

BTW Does this discussion take us anywhere?

Yes. We are one step closer to spoonfeeding.

DECLARE	@Sample TABLE
	(
		Item CHAR(1),
		Volume TINYINT
	)

INSERT	@Sample
SELECT	'A', 1 UNION ALL
SELECT	'A', 2 UNION ALL
SELECT	'B', 3 UNION ALL
SELECT	'B', 2 UNION ALL
SELECT	'C', 8 UNION ALL
SELECT	'C', 1

DECLARE	@Stage TABLE
	(
		RowID INT IDENTITY(1, 1),
		Item CHAR(1),
		Volume TINYINT
	)

INSERT		@Stage
		(
			Item,
			Volume
		)
SELECT		Item,
		SUM(Volume)
FROM		@Sample
GROUP BY	Item
ORDER BY	SUM(Volume) DESC

SELECT		s.Item,
		s.Volume,
		(SELECT SUM(x.Volume) FROM @Stage AS x WHERE x.RowID >= s.RowID) AS Incr
FROM		@Stage AS s

E 12°55'05.63"
N 56°04'39.26"

Thank You!
Best,
Agni

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-01 : 10:32:43

I'll wait right here until you spotted the typo in the suggestion.

E 12°55'05.63"
N 56°04'39.26"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-10-12 : 04:27:18

This kind of posters think that they are doing a big big favour by posting their problems & Peso,Visakh,Madhivanan & many other SQL gurus like them in this forum have a duty to solve their problems.
I wish no one should give solutions to their probelms & ppl like agniwoni be blacklisted.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-10-12 : 09:18:38

Shame SQL Server 2008 still doesn't support the frame clause of OVER. Then you could just write something like:


SELECT ITEM, SUM_VOL,
  SUM(SUM_VOL) OVER (ORDER BY SUM_VOL DESC ROW UNBOUNDED PRECEDING) AS SUM_INCR
FROM (
    SELECT ITEM, SUM(VOLUME) AS SUM_VOL
    FROM T
    GROUP BY ITEM
  ) AS T
ORDER BY SUM_VOL DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-12 : 13:36:16

[code];With Yak_CTE (Seq,Item,VolSum) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY SUM(VOLUME)),
ITEM,
SUM(VOLUME)
FROM YourTable
GROUP BY ITEM
)

SELECT y1.Item,
y1.VolSum AS SUM_VOL,
y2.SUM_INCR
FROM Yak_CTE y1
CROSS APPLY (SELECT SUM(VolSum) AS SUM_INCR
FROM Yak_CTE
WHERE Seq<=y1.Seq)y2
ORDER BY y1.Seq[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources