SUM Value Row by Row in SSMS - 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 2008 Forums

Transact-SQL (2008)

SUM Value Row by Row in SSMS

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2013-05-25 : 10:50:45

Hi
i have amount column based based on Monthyear.amount column is the sumvalue. based on amt field need YEARWISE FIELD IN THE Below criteria.how to do summation on row by row.

if anyone know help me

---monthwise -----year wise
amt monthyear yearewise
28.90 2013-01 28.90
25.78 2013-02 54.68
45.23 2013-03 99.21

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-25 : 12:02:42

[code]
;With CTE
AS
(
Your Current query with amount field
)

SELECT *
FROM CTE c
CROSS APPLY (SELECT SUM(amt) AS YrWise
FROM CTE
WHERE REPLACE(monthyear,'-','') * 1 < = REPLACE(c.monthyear,'-','')
)c1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-25 : 12:13:12

Here is another way:
[CODE]

DECLARE @AccTbl TABLE(amt MONEY, monthyear NVARCHAR(8))
INSERT INTO @AccTbl
SELECT 28.90, '2013-01' union all
SELECT 25.78, '2013-02' union all
SELECT 45.23, '2013-03' union all
SELECT 28.90, '2013-04' union all
SELECT 25.78, '2013-08' union all
SELECT 45.23, '2013-12' union all
SELECT 28.90, '2014-01' union all
SELECT 25.78, '2014-02' union all
SELECT 45.23, '2014-03';

SELECT
amt,
monthyear,
yearwise = amt + COALESCE(
(
SELECT SUM(amt)
FROM @AccTbl AS S
WHERE SUBSTRING(S.monthyear,1, 4) = SUBSTRING(O.monthyear,1, 4) AND
SUBSTRING(S.monthyear, 6, 2) < SUBSTRING(O.monthyear, 6, 2)), 0
)
FROM @AccTbl AS O
ORDER BY SUBSTRING(monthyear,1, 4),
SUBSTRING(monthyear, 6, 2)

[/CODE]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-05-30 : 04:39:54

Alternatively if you show these data in a front end application, you can do this there too

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources