count in where condition ? - 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

count in where condition ?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-09-09 : 04:57:01

HI

I have table (tbl1) and columns(ID, catId, Cat)
column 'Cat' have data as below

CatID ---------Cat
23------------SLA
24------------WAREHOUSE
25 -----------SLA
220-----------PRODUCTS
123------------RETAIL
115------------WAREHOUSE

How do I select as below

Select catID,Cat From tbl1 WHERE Count(Cat) > 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-09 : 05:04:10

[code]select t1.catId, t1.cat from tbl1 as t1
inner join
(
Select Cat From tbl1
group by Cat having Count(Cat) > 1
) as t2 on t1.cat=t2.cat
[/code]

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-09-09 : 05:19:05

Hi

Thanks a lot

quote:
Originally posted by madhivanan

select t1.catId, t1.cat from tbl1 as t1
inner join
(
Select Cat From tbl1
group by Cat having Count(Cat) > 1
) as t2 on t1.cat=t2.cat

Madhivanan

Failing to plan is Planning to fail

onlyforme
Starting Member

25 Posts

Posted - 2008-09-09 : 06:10:23

quote:
Originally posted by Vaishu

HI

I have table (tbl1) and columns(ID, catId, Cat)
column 'Cat' have data as below

CatID ---------Cat
23------------SLA
24------------WAREHOUSE
25 -----------SLA
220-----------PRODUCTS
123------------RETAIL
115------------WAREHOUSE

How do I select as below

Select catID,Cat From tbl1 WHERE Count(Cat) > 1

onlyforme
Starting Member

25 Posts

Posted - 2008-09-09 : 06:11:38

select * from tablename where 1<=(select count(cat) from tablename)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-09 : 08:02:45

quote:
Originally posted by onlyforme

select * from tablename where 1<=(select count(cat) from tablename)

Well. What does this return if the condition is true?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources