Click here to Skip to main content
12,623,511 members (28,258 online)
Rate this:
 
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 1:17am
Updated 28-May-12 1:48am
v2
Comments
losmac 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 28-May-12 7:51am
   
My 5!
losmac 28-May-12 7:54am
   
Thank you, Prasad ;)
Sandeep Mewara 29-May-12 14:15pm
   
5+
losmac 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 28-May-12 6:45am
   
Why INSERT?
In opinion it should be UPDATE command.
vangapallynaveen 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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161128.1 | Last Updated 28 May 2012
Copyright © CodeProject, 1999-2016
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