13,772,369 members
See more:
HI all !!!

I have some values in table and i need to calculate the sum as follows

MonthlyContri          ||      Arrears          ||      total      ||     MonthlyTotal

1000           ||          0            ||    1000                  1000
1000           ||          0            ||    1000         ||       2000
1000           ||         2000          ||    3000         ||       5000
1000           ||         1000          ||    2000         ||       7000

MonthlyContri +Arrears = total

Now Monthly total = Last MonthlyTotal(in the previous row) +TotalNow
for ex here Monthly total in second row = 1000 +1000 =2000
third row =  2000 + 3000= 5000

Posted 28-May-12 1:17am
Updated 28-May-12 1:48am
v2
Maciej Los 28-May-12 6:53am

The problem was defined rather vaguely... On the first look, the question is incomplete. How we can help you, if your table does not contain field(column) with date? In my opinion you should use PIVOT table, like this:
NameOfSomething || 1 || 2 || 3
Fruits || 1000 || 2000 || 5000 || 7000
...

## Solution 2

Take a look at below example:
IF NOT OBJECT_ID(N'#Sale',N'U') IS NULL
DROP TABLE #Sale

CREATE TABLE #Sale (ProductName VARCHAR(30), CountOfPosition INT, TotalPrice FLOAT, SaleDate DATETIME)

INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1005, 5000.12, '2012-01-14')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1030, 5312.88, '2012-01-22')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 998, 4987.65, '2012-01-31')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1012, 5100.12, '2012-02-01')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1099, 5719.78, '2012-02-16')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 888, 4567.32, '2012-02-28')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1005, 5000.12, '2012-03-05')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 1030, 5312.88, '2012-03-12')
INSERT INTO #Sale (ProductName, CountOfPosition, TotalPrice, SaleDate)
VALUES('Fruits', 898, 4987.65, '2012-03-29')

DECLARE @cols VARCHAR(1000)
DECLARE @dt VARCHAR (2000)
DECLARE @pt VARCHAR(4000)

SET @cols = '[1],[2],[3]'

SET @dt = 'SELECT ProductName, TotalPrice, MONTH(SaleDate) AS SaleMonth ' +
'FROM #Sale ' +
'WHERE YEAR(SaleDate) = 2012 '
--EXEC(@dt)

SET @pt = 'SELECT ProductName, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT (SUM(TotalPrice) FOR SaleMonth IN (' + @cols + ')) AS PT ' +
'ORDER BY PT.ProductName'
EXEC (@pt)

DROP TABLE #Sale

Result:
ProductName	1	2	        3
Fruits	    15300,65	15387,22	15300,65

As you see, calculations are provided for each product (in this case only fruits) and for each month of year 2012.

To get total sum for each product, we need to change pivot query, removing ORDER BY clause:
SET @pt = 'SELECT ProductName, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT (SUM(TotalPrice) FOR SaleMonth IN (' + @cols + ')) AS PT ' -- +
--'ORDER BY PT.ProductName'
EXEC (@pt)

--declare variables
DECLARE @fq VARCHAR(4000)
DECLARE @cols2 VARCHAR (200)

--set variables to get total sum for months: 1-3
SET @cols2 = '[1] + [2] + [3]'
SET @fq = 'SELECT ProductName, ' + @cols + ', ' + @cols2 + ' AS TotalSum ' +
'FROM (' + @pt + ') AS FQ'
EXEC (@fq)

More at:
Using PIVOT and UNPIVOT[^]

Search this site to find more examples.
v2

My 5!
Maciej Los 28-May-12 7:54am

Sandeep Mewara 29-May-12 14:15pm

5+
Maciej Los 29-May-12 14:37pm

Thank you, Sandeep ;)

## Solution 1

Try Like this
Dim con As SqlConnection=New Sqlconnection("Your Connection string")
Dim cmd As SqlCommand

Dim ds As DataSet
con.open()
da = New SqlDataAdapter("select * from sample order by mtotal desc", con)
ds = New DataSet()
da.Fill(ds)
dim lasttotal as string =ds.tables(0).rows(0)("lasttotal").tostring()
dim total as integer=txtmcontr+arrears
dim monthlytotal as integer=convert.toint32(lasttotal)+total
cmd=New SqlCommand("insert into sample (moncontr,arrears,total,mtotal) values('"+txtmcontr+"','"+arrears
+"','"+total+"','"+monthlytotal+"')")
cmd.executenonquary()

con.close()
v4
Maciej Los 28-May-12 6:45am

Why INSERT?
In opinion it should be UPDATE command.
vangapally Naveen Kumar 28-May-12 6:53am

in his qustion he shown one example na on that he inserting the values to next row thats why i use insert statement
Technoses 28-May-12 7:25am

Why INSERT??
is his question he is retriving only data and i think not want to insert total in database

Top Experts
Last 24hrsThis month
 OriginalGriff 430 Richard MacCutchan 150 Vincent Maverick Durano 120 CHill60 110 CPallini 100
 OriginalGriff 3,862 Richard MacCutchan 1,925 CPallini 1,134 MadMyche 980 CHill60 635