Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
 
[edit]Code block added - OriginalGriff[/edit]
Posted 28-May-12 0:17am
Edited 28-May-12 0:48am
v2
Comments
losmac at 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
...
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
v2
Comments
Prasad_Kulkarni at 28-May-12 7:51am
   
My 5!
losmac at 28-May-12 7:54am
   
Thank you, Prasad ;)
Sandeep Mewara at 29-May-12 14:15pm
   
5+
losmac at 29-May-12 14:37pm
   
Thank you, Sandeep ;)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try Like this
 Dim con As SqlConnection=New Sqlconnection("Your Connection string")
    Dim cmd As SqlCommand 
 
Dim ds As DataSet
    Dim da As SqlDataAdapter
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()
  Permalink  
v4
Comments
losmac at 28-May-12 6:45am
   
Why INSERT?
In opinion it should be UPDATE command.
vangapallynaveen at 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 at 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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 BillWoodruff 314
1 Sergey Alexandrovich Kryukov 290
2 George Jonsson 274
3 CPallini 255
4 OriginalGriff 237
0 OriginalGriff 5,030
1 CPallini 4,115
2 Sergey Alexandrovich Kryukov 3,554
3 George Jonsson 2,826
4 Gihan Liyanage 2,386


Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 28 May 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100