Counting records that appear more than once

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

SQL Server Development (2000)

Counting records that appear more than once

Author

Topic

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-11 : 11:59:03

I think perhaps I have been sat here too long but I can't for the life of me work out how to return a single value which is a count of the number of records whose ContactId appears in a table 2 or more times. The query I have below returns rows for each record that appears more than once which is not the result I am after.


SELECT     TOP 100 PERCENT COUNT(ContactID) AS Expr1
FROM         dbo.Contact_Application
GROUP BY ContactID
HAVING      (COUNT(ContactID) > 1)

www.creativenrg.co.uk

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-11 : 12:04:27

If you need 2 or more times it would be :


SELECT contactId, COUNT(*) AS Expr1
FROM         dbo.Contact_Application
GROUP BY ContactID
HAVING      count(*) >= 2

If you need anything < 100 percent then it makes sence to add a TOP. TOP 100 PERCENT is kinda redundant. SQL Server will return all the results anyway. Specifying TOP and saying 100 Percent is confusing SQL Server and can affect performance.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-11 : 12:15:28

Thanks but this does not produce the results I was after as identified in my original post. I want an overall count, a single value, not a row for each record that has a count greater than or equal to two. So if there were 200 records that had 2 or more entries I would expect to receive the value '200' not 200 rows saying '2'.

Hopefully this clarifies the requirement a little better,

www.creativenrg.co.uk

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-11 : 12:19:43

[code]
select
count(*)
from
(
SELECT 1
FROM
dbo.Contact_Application
GROUP BY
ContactID
HAVING
count(*) >= 2
) a
[/code]

CODO ERGO SUM

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-11 : 12:29:46

quote:
Originally posted by Michael Valentine Jones

select
count(*)
from
(
SELECT 1
FROM
dbo.Contact_Application
GROUP BY
ContactID
HAVING
count(*) >= 2
) a

CODO ERGO SUM

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-11 : 12:58:24

quote:
Originally posted by dinakar

[quote]Originally posted by Michael Valentine Jones

select
count(*)
from
(
SELECT 1
FROM
dbo.Contact_Application
GROUP BY
ContactID
HAVING
count(*) >= 2
) a

That worked great thanks however I just took a call and the client now wishes to complicate it a little. How would I adapt this query so that it only counts contacts for a given application but takes into account all applications given that my table has the following structure


CREATE TABLE [dbo].[Contact_Application] (
	[ContactID] [int] NOT NULL ,
	[ApplicationId] [int] NOT NULL ,
	[UserId] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
	[Username] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
	[RegistrationDate] [smalldatetime] NULL 
) ON [PRIMARY]

So my expected results as an example will count all ContactId's that are in ApplicationId1 that have records for 2 or more applications.

Hope that makes sense?

www.creativenrg.co.uk

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-11 : 13:14:15

Some sample data and expected output would be great!

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-11 : 13:14:46

Add a WHERE condition.


select
	count(*)
from
	(
	SELECT 1
	FROM
		dbo.Contact_Application
        WHERE
               Applicationid = 'ApplicationId1'
	GROUP BY
		ContactID
	HAVING
		count(*) >= 2
	) a

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-11 : 13:15:49

If this doesnt work, as Peso mentioned, we need some sample data and expected output.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-23 : 11:06:06

quote:
Originally posted by dinakar

If this doesnt work, as Peso mentioned, we need some sample data and expected output.

Sorry for the delayed reply.
Sample data inserts can be found here

http://www.creativenrg.co.uk/dump/Inserts86249.txt

As an example; There are 936 contacts that are currently in the table and are associated with applicationid=1. I need to know how many of the 936 also belong to at least one other application in addition to this one.

I hope this makes sense now as I am really struggling to describe the situation any better. Please let me know if any further information is required.

Thanks, Simon

www.creativenrg.co.uk

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 11:18:52

[code]SELECT ContactID
FROM (
SELECT ContactID,
SUM(CASE WHEN ApplicationID = 1 THEN 1 ELSE 0 END) AS App1,
SUM(CASE WHEN ApplicationID <> 1 THEN 1 ELSE 0 END) AS OtherApp
FROM dbo.Contact_Application
GROUP BY ContactID
) AS d
WHERE App1 >= 1
AND OtherApp >= 1

SELECT ContactID
FROM dbo.Contact_Application
GROUP BY ContactID
HAVING SUM(CASE WHEN ApplicationID = 1 THEN 1 ELSE 0 END) >= 1
AND SUM(CASE WHEN ApplicationID <> 1 THEN 1 ELSE 0 END) >= 1

SELECT ContactID
FROM @Contact_Application
GROUP BY ContactID
HAVING MAX(CASE WHEN ApplicationID = 1 THEN 1 ELSE 0 END) = 1
AND MIN(CASE WHEN ApplicationID = 1 THEN 1 ELSE 0 END) = 0[/code]
Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-23 : 11:40:23

What do you mean by >>>how many of the 936 also belong to at least one other application in addition to this one.

Is it by contactId or by userid/username? Can you provide expected output?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-23 : 12:36:23

quote:
Originally posted by dinakar

What do you mean by >>>how many of the 936 also belong to at least one other application in addition to this one.

Is it by contactId or by userid/username? Can you provide expected output?

ContactId is the unqiue identifier for a contact.
Not sure what expected output I can provide since the only thing I am expecting the query to return is a single integer which is the total number of contacts for a given application that are also associated with other applications. Very sorry if I am still not making this clear.

www.creativenrg.co.uk

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-23 : 12:39:58

[code]SELECT COUNT(*) AS NumberOfContacts
FROM (
SELECT ContactID,
MAX(CASE WHEN ApplicationID = 1 THEN 1 ELSE 0 END) AS App1,
MAX(CASE WHEN ApplicationID = 1 THEN 0 ELSE 1 END) AS OtherApp
FROM dbo.Contact_Application
GROUP BY ContactID
) AS d
WHERE App1 = 1
AND OtherApp = 1[/code]
Peter Larsson
Helsingborg, Sweden

CreativeNRG
Starting Member

44 Posts

Posted - 2007-07-24 : 04:30:26

quote:

SELECT		COUNT(*) AS NumberOfContacts
FROM		(
			SELECT		ContactID,
					MAX(CASE WHEN ApplicationID = 1 THEN 1 ELSE 0 END) AS App1,
					MAX(CASE WHEN ApplicationID = 1 THEN 0 ELSE 1 END) AS OtherApp
			FROM		dbo.Contact_Application
			GROUP BY	ContactID
		) AS d
WHERE		App1 = 1
		AND OtherApp = 1

If my understanding of this is correct this query assumes that there is only 2 applications? How would this accomodate x number of applications? I am also not sure I understand exactly what this query is doing since the 2 lines in the sub query seem to both be comparing applicationid = 1 ?

www.creativenrg.co.uk

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 07:28:33

App1 returns 1 if a contact has at least one application with id = 1.
OtherApp returns 1 if a contact has at least one application with id not equal to 1.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources