Query - 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

Query

Author

Topic

ruchijani
Starting Member

23 Posts

Posted - 2009-09-18 : 08:11:10

Hello

I have Table like this


Name     Col1        Col2       Col3
abc       1           2          4
abc       3           4          1
abc       2           2          2
xyz       5           1          2
xyz       1           4          6

and i want table like this way


Name     Col1        Col2       Col3    Col4
abc       1           2          4       3
abc       3           4          1       3
abc       2           2          2       3
xyz       5           1          2       2
xyz       1           4          6       2

where Col4 has ROWCOUNT of NAME group by NAME like
NAME 'abc' has 3 ROWCOUNT and xyz has 2 ROWCOUNT

Thanks
Ruchi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-18 : 08:16:15

Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-18 : 08:16:49

try this...

select a.*,b.col4 from Table1 a
cross apply (select count([Name]) as col4,[Name] from Table1 group by [Name]) b
where a.[Name] = b.[Name]

ruchijani
Starting Member

23 Posts

Posted - 2009-09-18 : 08:23:01

Hi madhivanan

I m using SQL SERVER 2000

Thanks
Ruchi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-18 : 08:26:08

Ok. Try this

select a.*,b.col4 from Table1 a
inner join (select count([Name]) as col4,[Name] from Table1 group by [Name]) b
where a.[Name] = b.[Name]

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-18 : 08:31:17

quote:
Originally posted by madhivanan

Ok. Try this

select a.*,b.col4 from Table1 a
inner join (select count([Name]) as col4,[Name] from Table1 group by [Name]) b
where a.[Name] = b.[Name]

Madhivanan

Failing to plan is Planning to fail

minor change

select a.*,b.col4 from Table1 a
inner join (select count([Name]) as col4,[Name] from Table1 group by [Name]) b
on a.[Name] = b.[Name]

ruchijani
Starting Member

23 Posts

Posted - 2009-09-18 : 08:41:04

Thank u vijay

quote:

quote:
--------------------------------------------------------------------------------
Originally posted by madhivanan

Ok. Try this

select a.*,b.col4 from Table1 a
inner join (select count([Name]) as col4,[Name] from Table1 group by [Name]) b
where a.[Name] = b.[Name]

Madhivanan

Failing to plan is Planning to fail

--------------------------------------------------------------------------------

minor change

select a.*,b.col4 from Table1 a
inner join (select count([Name]) as col4,[Name] from Table1 group by [Name]) b
on a.[Name] = b.[Name]

Thanks
Ruchi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-18 : 08:49:23

quote:
Originally posted by vijayisonly

quote:
Originally posted by madhivanan

Ok. Try this

select a.*,b.col4 from Table1 a
inner join (select count([Name]) as col4,[Name] from Table1 group by [Name]) b
where a.[Name] = b.[Name]

Madhivanan

Failing to plan is Planning to fail

minor change

select a.*,b.col4 from Table1 a
inner join (select count([Name]) as col4,[Name] from Table1 group by [Name]) b
on a.[Name] = b.[Name]

Thanks

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-18 : 10:29:02

quote:
Originally posted by madhivanan

quote:
Originally posted by vijayisonly

quote:
Originally posted by madhivanan

Ok. Try this

select a.*,b.col4 from Table1 a
inner join (select count([Name]) as col4,[Name] from Table1 group by [Name]) b
where a.[Name] = b.[Name]

Madhivanan

Failing to plan is Planning to fail

minor change

select a.*,b.col4 from Table1 a
inner join (select count([Name]) as col4,[Name] from Table1 group by [Name]) b
on a.[Name] = b.[Name]

Thanks

Madhivanan

Failing to plan is Planning to fail

np...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources