count included for each record

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 included for each record

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-11-14 : 15:43:56

I am looking to count the # of duplicates within a table and include this count with each associated record. I want the result to look like this:

ColumnA / COUNT
1 / 3
1 / 3
1 / 3
4 / 2
4 / 2
22 / 4
22 / 4
22 / 4
22 / 4

Does anyone have any ideas?
Cheers,
Dirwin

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-11-14 : 16:06:51

paste your code here

But u can use the count(column_name) to get this done

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-14 : 16:13:55

Sure, but of what value would that be?


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int)
GO

INSERT INTO myTable99(Col1)
SELECT 1  UNION ALL SELECT 1  UNION ALL SELECT 1  UNION ALL
SELECT 4  UNION ALL SELECT 4  UNION ALL
SELECT 22 UNION ALL SELECT 22 UNION ALL SELECT 22 UNION ALL SELECT 22
GO

SELECT l.Col1, r.NumDups 
  FROM myTable99 l
  JOIN (SELECT Col1, COUNT(*) AS NumDups 
	  FROM myTable99 
      GROUP BY Col1) AS r
    ON l.Col1 = r.col1
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-11-14 : 16:18:49

The columns are dynamic, you never know what they are gong to be, so I guess I would nee to create variables for the above statement.

THanks,
Diriwn

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-14 : 16:28:25

quote:
Originally posted by dirwin26

The columns are dynamic, you never know what they are gong to be, so I guess I would nee to create variables for the above statement.

THanks,
Diriwn

Huh?

Read the hint link in my sig and post some info to help us help you.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-11-14 : 16:57:25

In your code you used specific values in the union statements. I won't be able to create statements manually each time. I am running queries off of a temp table (which has about 10,000 rows) which is accessed via asp so the values which I placed above
(1 / 3
4 / 2
22 / 4)
will always be changing. Does this make sense?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-11-14 : 17:19:32

The UNIONs were used to populate the table with sample data... is that what you are referring to?

Nathan Skerl

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-11-14 : 17:21:01

Brett provided the solution already. You can run his example as-is in Query Analyzer to see it with a dummy table. To make it work for your situation:

SELECT l.Col1, r.NumDups
FROM myTable99 l
JOIN (SELECT Col1, COUNT(*) AS NumDups
FROM myTable99
GROUP BY Col1) AS r
ON l.Col1 = r.col1

Change myTable99 to the name of your table and also change the column names.

Tara Kizer
aka tduggan

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-11-14 : 20:04:35

That worked, thanks! I don't fully understand the JOIN code to be honest. Can you tell me how I can now delete NumDups if its' count is below 5?

Cheers,
Dirwin

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-15 : 00:52:12

Delete M from myTable99 M where exists
(Select col1 from myTable99 where col1=M.col1 group by col1 having count(*)<5)

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-15 : 10:14:54

quote:
Originally posted by dirwin26

That worked, thanks! I don't fully understand the JOIN code to be honest. Can you tell me how I can now delete NumDups if its' count is below 5?

Cheers,
Dirwin

Whoa...slow down...got a backup of the table first?

Also, usually the request is to delete all dups, except for the "first" or "last" one.....

Why don't you describe to us, in business requirement terms, what needs to be done...and please read my hint link in my signature....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-11-15 : 12:02:11

I analyze financial data. I look at the year over year growth rates that companies report on a quarterly basis. I push data in sql forward by 12 months to perform the proper y/y growth rates. However b/c of leap years etc the year over year return calculation is off sometimes by a couple days, which causes me to get unique numbers in the middle of about 60 like numbers. I don't want to delete duplicates, my program needs duplicates. So what I wanted to do was to find these unique numbers, or numbers where the duplicates are less than 5 and delete them, hence the count<5. The above code actually does what I want it to do, but the result in my stored procedure includes the JOIN data which is messing up my graphing. So now my problem is performing the join without including it within the results of the SP, which occurs b/c of the select.

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-11-15 : 17:19:55

This allows for the use of variables for those interested

DECLARE @1 DATETIME
DECLARE @2 BIGINT
DECLARE @3 BIGINT

SELECT @1=thedate, @2=l.rtgr, @3=r.NumDups
FROM #res l
JOIN (SELECT rtgr, COUNT(*) AS NumDups
FROM #res
GROUP BY rtgr) AS r
ON l.rtgr = r.rtgr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources