How to query this requirement - 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

SQL Server 2005 Forums

Transact-SQL (2005)

How to query this requirement

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-04-13 : 04:45:30

I have one table with data as below. First row specifies column headings.

Zcode INDI JPAN ASIM CALC
165-34 0 4 7 9
165-64 9 3 2 7
165-90 7 2 1 9
165-76 3 3 6 0
145-90 4 2 1 9
567-09 3 4 4 7

From table 1, i need to retrive rows of similar(For example here is 165-#). Now i want to insert new row to same table with Zcode as 165
To insert this,the condition here is look for all rows which starts with 165 and look for distinct number in CALC. Here now we got 3 rows(1st,2nd&4th). Now Add all three rows data and give final output as

The New row should be like this
Zcode INDI JPAN ASIM CALC
165 12 10 15 16

The addition of all INDI, JPAN ASIM, CALC Columns. Similar way, the same thing will check for all rows in table1.

G. Satish

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-13 : 05:28:48

select SUM(distinct calc),left(zcode,3) from your table group by left(zcode,3).
I did it just for column CALC u can do the same for other columns to.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-04-13 : 05:28:57

Is this you r looking for?

select zip, sum(indi) INDI,sum(jpan) JPAN,sum(asim) ASIM,sum(calc) CALC
from (
select left(zcode,3)as zip ,indi,jpan,asim,calc,
row_number() over(partition by left(zcode,3),calc order by zcode,calc) as row
from @a
) a where row =1 group by zip

Karthik

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-13 : 08:12:31

try this too
declare @tab table (Zcode varchar(32), INDI INT,JPAN INT,ASIM INT,CALC INT)
insert into @tab select
'165-34',0,4,7,9 union all select
'165-64',9,3,2,7 union all select
'165-90',7,2,1,9 union all select
'165-76',3,3,6,0 union all select
'145-90',4,2,1,9 union all select
'567-09',3,4,4,7

select substring(zcode,1,charindex('-',zcode,1)-1)as zcode,sum(indi)as indi,sum(jpan) as jpan,sum(asim) as asim, sum(calc) as calc
from (
select row_number() over(partition by left(zcode,charindex('-',zcode,1)-1),calc order by zcode) as rid, * from @tab) s
where s.rid = 1 and left(zcode,charindex('-',zcode,1)-1)= 165
group by substring(zcode,1,charindex('-',zcode,1)-1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources