FIND category - 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

FIND category

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-03-27 : 18:09:08

how can i find only the category that has only one catnumber = 1008 and not more than 2 catnumbers?
result: 3x

declare @Table1 table (category varchar(50) , catnumber int)
insert @Table1
select '1x', '10008' union all
select '1x', '5' union all
select '2x', '10008' union all
select '2x', '2' union all
select '3x', '10008' union all
select '4x', '2'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 19:41:53

[code]
SELECT category
FROM @table1
GROUP BY category
HAVING COUNT(DISTINCT catnumber)<=2
AND SUM(CASE WHEN catnumber=1008 THEN 1 ELSE 0 END) =1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2012-04-11 : 16:14:03

Hi V,

I ran your query but it doesnt show me any result. Result should be 3x

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 00:20:48

thats because you had wrong value specified in thread

as per posted data it should be


SELECT category
FROM @table1
GROUP BY category
HAVING COUNT(DISTINCT catnumber)<=2
AND SUM(CASE WHEN catnumber=10008 THEN 1 ELSE 0 END) =1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-12 : 06:18:21

this?


Select category From @Table1
Group By category
Having COUNT(Category) = 1

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources