Count only if different - 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 2000 Forums

Transact-SQL (2000)

Count only if different

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2006-04-04 : 13:42:51

I have table tbl1 with 2 fields C_Id and C_C_Id. I would like to run a SQL to count for each C_Id only those with more than 2 different C_C_Id ?

Say

1 1
1 1
1 2
1 4
2 1
2 1

Should just return 1 3

jean-luc
www.corobori.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 13:54:45

Try this:


DECLARE @Table1 table (Column1 int, Column2 int)

INSERT INTO @Table1 VALUES(1,1)
INSERT INTO @Table1 VALUES(1,1)
INSERT INTO @Table1 VALUES(1,2)
INSERT INTO @Table1 VALUES(1,4)
INSERT INTO @Table1 VALUES(2,1)
INSERT INTO @Table1 VALUES(2,1)

SELECT Column1, COUNT(*)
FROM (SELECT DISTINCT Column1, Column2 FROM @Table1) t
GROUP BY Column1
HAVING COUNT(*) > 2

Tara Kizer
aka tduggan

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-04 : 14:22:17

Hi all,

This is possible too. It appears to produce the same query plan as yours, Tara, so I guess syntactic brevity might be the only benefit...

DECLARE @Table1 table (Column1 int, Column2 int)

INSERT INTO @Table1 VALUES(1,1)
INSERT INTO @Table1 VALUES(1,1)
INSERT INTO @Table1 VALUES(1,2)
INSERT INTO @Table1 VALUES(1,4)
INSERT INTO @Table1 VALUES(2,1)
INSERT INTO @Table1 VALUES(2,1)

SELECT Column1, COUNT(DISTINCT Column2)
FROM @Table1
GROUP BY Column1
HAVING COUNT(DISTINCT Column2) > 2

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 14:37:26

Yes they are identical. I prefer the derived table approach though. Jeff explains it well in the Derived table section of this blog:

http://weblogs.sqlteam.com/jeffs/archive/2006/03/14/9289.aspx

Tara Kizer
aka tduggan

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-04 : 14:56:15

Hi Tara,

That sure is a good blog - though I must admit, I don't see the relevance of the derived table section to this thread.

Can you please explain why you prefer the derived table approach when you can do the query with just one simple, shorter select?

Maybe I'm missing something...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-04 : 14:59:01

Because I prefer not to have COUNT(DISTINCT Column2) in the HAVING and SELECT portion. IMO, mine is easier to understand since you run the derived table portion separately. It's my coding preference, that's all.

Tara Kizer
aka tduggan

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-05 : 04:31:24

Okay - fair enough. Thanks for the explanation.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources