Addition of two rows - 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

Addition of two rows

Author

Topic

david_reinjal
Starting Member

36 Posts

Posted - 2007-03-22 : 00:54:57

hi guys,

I have got something like this,

A S D

Bi Ar 23
Bi Ar 12
As Ar 52
As Ar 11
Ap Jo 24

I Have 3 columns. First 2 columns are Text and the third one is Number. I need to see if any duplicate values are there in first 2 columns. If so then i need to add the values of 3 columns and make it one. So the output should look like this,

A S D

Bi Ar 35
As Ar 63
Ap Jo 24

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 00:57:56

[code]
selet A, S, D = sum(D)
from yourtable
group by A, S
[/code]

david_reinjal
Starting Member

36 Posts

Posted - 2007-03-22 : 01:08:12

hey it says, " You tried to execute a query that does not include the specified expression ' D= sum(D)' as part of an aggregate function". What should i do?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 01:16:39

[code]
declare @table table
(
A char(2),
S char(2),
D int
)
insert into @table
select 'Bi', 'Ar', 23 union all
select 'Bi', 'Ar', 12 union all
select 'As', 'Ar', 52 union all
select 'As', 'Ar', 11 union all
select 'Ap', 'Jo', 24

select A, S, D = sum(D)
from @table
group by A, S

/*

A S D
---- ---- -----------
As Ar 63
Bi Ar 35
Ap Jo 24

*/
[/code]

Please post your actual query.

david_reinjal
Starting Member

36 Posts

Posted - 2007-03-22 : 01:24:50

i cant use this method because i have 1060 entries. is there someother to do?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 01:28:10

the declare @table table is just for demonstrate the working of the query
Just change the table name, column name to your actual


select	A, S, D = sum(D)
from	youractualtablename
group by A, S

david_reinjal
Starting Member

36 Posts

Posted - 2007-03-22 : 01:35:49

ya man i did that, but its still giving the same problem. this is the query i am trying to run,

SELECT FirseName, LastName, Salary = sum(Salary)
FROM [Employees]
GROUP BY FirseName, LastName;

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 01:38:49

the query looks perfectly fine to me. Please post the exact error message

david_reinjal
Starting Member

36 Posts

Posted - 2007-03-22 : 01:46:58

it says, " You tried to execute a query that does not include the specified expression 'Salary= sum(Salary)' as part of an aggregate function".

david_reinjal
Starting Member

36 Posts

Posted - 2007-03-22 : 01:53:35

this is how my database looks and its a MS Access Database.

FirstName LastName Salary
Jack Wilson 2312
Jack Wilson 3432
Rey M 5615
Rey M 45768
David Reinjal 3211

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 01:55:53

I have no idea why you would get that error. What is the version of SQL Server ?


create table [Employees]
(
	FirseName 	char(2),
	LastName 	char(2),
	Salary		int
)
insert into [Employees]
select	'Bi', 'Ar', 23	union all
select	'Bi', 'Ar', 12	union all
select	'As', 'Ar', 52	union all
select	'As', 'Ar', 11	union all
select	'Ap', 'Jo', 24

SELECT 	FirseName, LastName, Salary = sum(Salary)
FROM 	[Employees]
GROUP BY FirseName, LastName

/*
FirseName LastName Salary      
--------- -------- ----------- 
As        Ar       63
Bi        Ar       35
Ap        Jo       24
*/

drop table [Employees]

david_reinjal
Starting Member

36 Posts

Posted - 2007-03-22 : 01:58:55

its not SQL, its MS Access DB.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-22 : 02:00:16

[code]SELECT FirseName, LastName, sum(Salary) as Sal
FROM Employees
GROUP BY FirseName, LastName[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 02:01:33

no wonder. This is the wrong forum for MS Access. This is "New to SQL Server forum". The query i posted works perfectly in SQL Server.

You should post in http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

david_reinjal
Starting Member

36 Posts

Posted - 2007-03-22 : 02:10:36

thnx harsh. its worked. sorry khtan i dint knew where to post. thnx anyway for ur replies and ur efforts.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 02:37:19

sorry MS Access is totally alien to me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources