Need to Count Record Occurences

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)

Need to Count Record Occurences

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-11-29 : 13:54:43

I am running SQL Server 2005. I have a join table with the following structure:

CREATE TABLE [dbo].[my_join_table](
	[product_id] [int] NOT NULL,
	[feature_id] [int] NOT NULL,
)

This table links a product with a product feature. There are no constraints on this table, so there can be an unlimited number of instances for each [product_id] and [feature_id]. I need to write a query that shows me the counts of features, so I can essentially be able to know the number of features per product. I also need to be able to show which product(s) have the highest number of features.

I know how I could do this with one or two CURSORS, but that would be highly inefficient in this case. Can someone help me write this query?

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-29 : 15:41:45

you should have a primary key (or unique constraint) on these 2 columns. Since you don't I used distinct count
This will show you the feature count by product

select product_id, count(distinct feature_id)
from my_join_table
group by product_id
order by count(distinct feature_id) desc

Be One with the Optimizer
TG

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-11-29 : 15:48:38

Whoops. I do have one. Sorry. Here it is:

CREATE TABLE [dbo].[my_join_table](
	[join_id] [int] IDENTITY(1,1) NOT NULL,
	[product_id] [int] NOT NULL,
	[feature_id] [int] NOT NULL
)

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-29 : 16:22:26

an identity column is not a constraint. It will not prevent duplicate product_id,feature_id combinations. It will just provide uniqueness to the row.

Be One with the Optimizer
TG

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-11-29 : 16:41:35

But how would that alter your original suggestion? I do have that column. Your post suggested that not having the column altered how you were going to suggest the query be done.

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-29 : 17:04:32

>>But how would that alter your original suggestion?

If you had a unique constraint on (feature_id, product_id) then you could remove the 2 occurances of the word "distinct" from my original post and still get an accurate count because you would be certain to have a distinct list of features per product in your table.

right now you could have 100 rows all with the same [product_id] and [feature_id] even though you would have 100 unique [Join_id]s. See what I mean?

Be One with the Optimizer
TG

hismightiness
Posting Yak Master

164 Posts

Posted - 2007-11-29 : 17:12:26

Ahhh... I do see what you mean. Thanks for the tip. I am going to implement that. I appreciate it.

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources