Please advice .. :( - 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

Please advice .. :(

Author

Topic

John_Mc_2009
Starting Member

7 Posts

Posted - 2008-12-04 : 15:43:00

Hi all,

I am trying to insert data in one record on different columns as per my AGE value. But with below query, I am getting the records in 3 rows, if there are values for age which are matching in CASE statement.

insert into employee
(id, empname,avg2008,avg2009, av2010, datecreated)
select tempid,
empname,
CASE when age = 20 then sum(salary) end,
CASE when age = 40 then sum(salary) end,
CASE when age = 60 then sum(salary) end,
getdate() from logemployee
group by age;

Please advice.. :(

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-04 : 16:35:08

Try this.. (I removed the two extra END words)
I think you only need one END. just a guess

insert into employee
(id, empname,avg2008,avg2009, av2010, datecreated)
select tempid,
empname,
CASE when age = 20 then sum(salary)
CASE when age = 40 then sum(salary)
CASE when age = 60 then sum(salary)
end,
getdate() from logemployee
group by age;

John_Mc_2009
Starting Member

7 Posts

Posted - 2008-12-04 : 16:44:56

Revdnrdy,

That will not help... I need to insert into each column..not into one column....

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-04 : 16:58:14

are you trying to insert the SUM of these 3 age groups?? Please illustrate:


select tempid, empname, 
(select sum(salary) from logemployee where age = 20),
(select sum(salary) from logemployee where age = 40),
(select sum(salary) from logemployee where age = 60),
getdate() from logemployee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 02:34:49

did you mean this?

insert into employee
(id, empname,avg2008,avg2009, av2010, datecreated)
select tempid, 
empname,
SUM(CASE when age = 20 then salary else 0 end),
SUM(CASE when age = 40 then salary else 0 end),
SUM(CASE when age = 60 then salary else 0 end),
getdate() from logemployee 
group by tempid, 
empname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources