Count within a subquery - 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

SQL Server Development (2000)

Count within a subquery

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2003-05-01 : 01:06:36

I have 2 tables, one that stores a list of emails newsletters, and another that stores information on what users have opened what email. 1 user can open an email more than once, and it will be written to the table each time. There will be some areas where I want to see how many times each user opened the email, but for this query I only want to see how many distinct users opened the email. Below is the code to create a simplified version of the tables, and the query I have so far:

CREATE TABLE Emails (
	emailID int,
	subject varchar(250),
	body varchar(1000)
)
GO

INSERT INTO Emails (emailID, subject, body) VALUES (1,'Subject line', 'Newsletter body')
GO

CREATE TABLE EmailOpens (
	openID int,
	emailID int,
	subscriberID int
)

GO

INSERT INTO EmailOpens (openID, emailID, subscriberID) VALUES (1,1,1)
GO
INSERT INTO EmailOpens (openID, emailID, subscriberID) VALUES (2,1,2)
GO
INSERT INTO EmailOpens (openID, emailID, subscriberID) VALUES (3,1,1)
GO

SELECT
	e.emailID,
	e.subject,
	opens = (SELECT COUNT(subscriberID) FROM EmailOpens eo WHERE e.emailID = eo.emailID)
FROM
	emails e

GO

DROP TABLE EmailOpens
GO
DROP TABLE Emails
GO

The problem with this is that it returns that there has been 3 opens, when I only want to show that the email has been opened by 2 users (I also have the number of recipients and want to work out the percentage of emails opened).

Any ideas?

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-01 : 01:36:01

SELECT

e.emailID,

e.subject,

COUNT (DISTINCT subscriberID) as Opens

FROM

emails e
INNER JOIN EmailOpens eo
ON e.emailID = eo.emailID

I think the INNER JOIN is easier to read than the SELECT . However, you could add the DISTINCT qualifier to your existing query and it should work fine.

HTH

Sam

benricho
Yak Posting Veteran

84 Posts

Posted - 2003-05-01 : 01:39:01

Thanks Sam, I didn't realize you could use DISTINCT in COUNT, works like a charm. And I agree, and inner join does look easier to read.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources