calculate sum in sql - 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

General SQL Server Forums

New to SQL Server Programming

calculate sum in sql

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2008-07-17 : 03:28:40

I have table follow that :
Name | Age
A | 23
B | 36
C | 37
D | 23
...
Z | 33
Y | 18

Now i want to sql statement calculate as:
Name | Age
A+B | 59
B+C | 73
C+D
.....

So how to do that ?
Thank you very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 03:31:10

is the A+B and B+C fixed ? Any possibility of A+C or any other combinition ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 03:37:01

quote:
Originally posted by pamyral_279

I have table follow that :
Name | Age
A | 23
B | 36
C | 37
D | 23
...
Z | 33
Y | 18

Now i want to sql statement calculate as:
Name | Age
A+B | 59
B+C | 73
C+D
.....

So how to do that ?
Thank you very much.

do you mean summing in order of arrival or on basis of someother field?

pamyral_279
Posting Yak Master

143 Posts

Posted - 2008-07-17 : 06:55:32

Thanks all,
I simply sum of column age in order of "before" and "after" .
A+B mean that : 23 + 36=59
B+C mean that :36+ 37=73
C+D mean that : 37+23=60
and so on ..

A+B is consider as :"(A+B)" or anything if you like.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-17 : 07:01:24

that would imply alphabetical order on the NAME ...? that can't right surely?

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-17 : 07:27:19

[code]DECLARE @TABLE TABLE
(
Name CHAR(1),
Age int
)
INSERT INTO @TABLE
SELECT 'A' , 23 UNION ALL
SELECT 'B' , 36 UNION ALL
SELECT 'C' , 37 UNION ALL
SELECT 'D' , 23 UNION ALL
SELECT 'Z' , 33 UNION ALL
SELECT 'Y' , 18

;WITH yak(Name, Age, row_no)
AS
(
SELECT Name, Age, row_no = row_number() OVER( ORDER BY Name)
FROM @TABLE
)
SELECT [Name] = y1.Name + '+' + y2.Name,
Age = y1.Age + y2.Age
FROM yak y1
INNER JOIN yak y2 ON y1.row_no = y2.row_no - 1
/*
Name Age
---- -----------
A+B 59
B+C 73
C+D 60
D+Y 41
Y+Z 51

(5 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 07:58:44

Also this:-

SELECT t.Name + '+'+b.Name,t.Age+b.Age
FROM @TABLE t
CROSS APPLY (SELECT TOP 1 *
             FROM @Table
             WHERE [Name] >t.Name
             ORDER BY [Name] )b

pamyral_279
Posting Yak Master

143 Posts

Posted - 2008-07-17 : 12:36:20

quote:

Originally posted by khtan

DECLARE	@TABLE TABLE
(
	Name	CHAR(1),
	Age	int
)
INSERT INTO @TABLE
SELECT	'A' , 23	UNION ALL
SELECT	'B' , 36	UNION ALL
SELECT	'C' , 37	UNION ALL
SELECT	'D' , 23	UNION ALL
SELECT	'Z' , 33	UNION ALL
SELECT	'Y' , 18

;WITH yak(Name, Age, row_no)
AS
(
	SELECT	Name, Age, row_no = row_number() OVER( ORDER BY Name)
	FROM	@TABLE
)
SELECT	[Name]  = y1.Name + '+' + y2.Name, 
	Age	= y1.Age + y2.Age
FROM	yak y1
	INNER JOIN yak y2	ON	y1.row_no	= y2.row_no - 1
/*
Name Age         
---- ----------- 
A+B           59 
B+C           73 
C+D           60 
D+Y           41 
Y+Z           51 

(5 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

I use sql2000 version which don't have row_number() function. What i have to do now ?
Thank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 12:45:47

quote:

Originally posted by pamyral_279

quote:

Originally posted by khtan

DECLARE	@TABLE TABLE
(
	Name	CHAR(1),
	Age	int
)
INSERT INTO @TABLE
SELECT	'A' , 23	UNION ALL
SELECT	'B' , 36	UNION ALL
SELECT	'C' , 37	UNION ALL
SELECT	'D' , 23	UNION ALL
SELECT	'Z' , 33	UNION ALL
SELECT	'Y' , 18

;WITH yak(Name, Age, row_no)
AS
(
	SELECT	Name, Age, row_no = row_number() OVER( ORDER BY Name)
	FROM	@TABLE
)
SELECT	[Name]  = y1.Name + '+' + y2.Name, 
	Age	= y1.Age + y2.Age
FROM	yak y1
	INNER JOIN yak y2	ON	y1.row_no	= y2.row_no - 1
/*
Name Age         
---- ----------- 
A+B           59 
B+C           73 
C+D           60 
D+Y           41 
Y+Z           51 

(5 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

I use sql2000 version which don't have row_number() function. What i have to do now ?
Thank

then try like this

DECLARE	@TABLE TABLE
(
	ID      identity(1,1),
        Name	CHAR(1),
	Age	int
)
INSERT INTO @TABLE (Name,Age)
SELECT	'A' , 23	UNION ALL
SELECT	'B' , 36	UNION ALL
SELECT	'C' , 37	UNION ALL
SELECT	'D' , 23	UNION ALL
SELECT	'Z' , 33	UNION ALL
SELECT	'Y' , 18

SELECT t1.Name + '+' + t2.Name AS [Name],t1.Age+t2.Age AS [Age]
FROM @TABLE t1
INNER JOIN @TABLE t2
ON t1.ID+1=t2.ID

pamyral_279
Posting Yak Master

143 Posts

Posted - 2008-07-18 : 00:39:50

quote:

then try like this

DECLARE	@TABLE TABLE
(
	ID      identity(1,1),
        Name	CHAR(1),
	Age	int
)
INSERT INTO @TABLE (Name,Age)
SELECT	'A' , 23	UNION ALL
SELECT	'B' , 36	UNION ALL
SELECT	'C' , 37	UNION ALL
SELECT	'D' , 23	UNION ALL
SELECT	'Z' , 33	UNION ALL
SELECT	'Y' , 18

SELECT t1.Name + '+' + t2.Name AS [Name],t1.Age+t2.Age AS [Age]
FROM @TABLE t1
INNER JOIN @TABLE t2
ON t1.ID+1=t2.ID

Thank you very much,your guide is very good, i would like to add datatype int in ID column.
I wonder if my table has many row about 1000 records,i can not write sql manual as your guide.


INSERT INTO @TABLE (Name,Age)
SELECT	'A' , 23	UNION ALL
SELECT	'B' , 36	UNION ALL
SELECT	'C' , 37	UNION ALL
SELECT	'D' , 23	UNION ALL
SELECT	'Z' , 33	UNION ALL
SELECT	'Y' , 18

So how to solve ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-18 : 03:21:29

quote:
Originally posted by pamyral_279

quote:

then try like this

DECLARE @TABLE TABLE
(
ID identity(1,1),
Name CHAR(1),
Age int
)
INSERT INTO @TABLE (Name,Age)
SELECT 'A' , 23 UNION ALL
SELECT 'B' , 36 UNION ALL
SELECT 'C' , 37 UNION ALL
SELECT 'D' , 23 UNION ALL
SELECT 'Z' , 33 UNION ALL
SELECT 'Y' , 18

SELECT t1.Name + '+' + t2.Name AS [Name],t1.Age+t2.Age AS [Age]
FROM @TABLE t1
INNER JOIN @TABLE t2
ON t1.ID+1=t2.ID

Thank you very much,your guide is very good, i would like to add datatype int in ID column.
I wonder if my table has many row about 1000 records,i can not write sql manual as your guide.

INSERT INTO @TABLE (Name,Age)
SELECT 'A' , 23 UNION ALL
SELECT 'B' , 36 UNION ALL
SELECT 'C' , 37 UNION ALL
SELECT 'D' , 23 UNION ALL
SELECT 'Z' , 33 UNION ALL
SELECT 'Y' , 18

So how to solve ?

It is just for demonstration
Dont use any union alls
Replace @table with your actual table name

Madhivanan

Failing to plan is Planning to fail

pamyral_279
Posting Yak Master

143 Posts

Posted - 2008-07-18 : 06:03:43

quote:

It is just for demonstration
Dont use any union alls
Replace @table with your actual table name

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan ! My real table don't have identity column.
I don't want to break structure of table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 06:08:33

quote:
Originally posted by pamyral_279

quote:

It is just for demonstration
Dont use any union alls
Replace @table with your actual table name

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan ! My real table don't have identity column.
I don't want to break structure of table.

dump data from your current table to a temporary table with an extra identity column and then use it

use
INSERT into TempTable
SELECT fields FROm YourTable

and then use TempTable in query given

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-18 : 11:03:41

[code]
DECLARE @TABLE TABLE
(
Name CHAR(1),
Age int
)
INSERT INTO @TABLE
SELECT 'A' , 23 UNION ALL
SELECT 'B' , 36 UNION ALL
SELECT 'C' , 37 UNION ALL
SELECT 'D' , 23 UNION ALL
SELECT 'Z' , 33 UNION ALL
SELECT 'Y' , 18

select t1.Name + ' + ' + t2.Name, t1.Age + t2.Age
from
(
SELECT Name, Age, row_no = (select count(*) from @TABLE x where x.Name <= t.Name)
FROM @TABLE t
) t1
INNER Join
(
SELECT Name, Age, row_no = (select count(*) from @TABLE x where x.Name <= t.Name)
FROM @TABLE t
) t2 ON t1.row_no = t2.row_no - 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

pamyral_279
Posting Yak Master

143 Posts

Posted - 2008-07-18 : 13:47:42

Thank all people here,
My problem has been solved ! This forum is useful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources