need member count with distinct multiple views

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 member count with distinct multiple views

Author

Topic

jwallz
Starting Member

14 Posts

Posted - 2009-02-20 : 18:36:12

i have a table, 'articleViews' with columns
id, articleID, MemberID, viewDate

I need to get a total count of members who have viewed at least 2 distinct articles. Can anyone help? I've been on this for 2 hours.
It would be greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-20 : 18:41:15

This might do it:

SELECT COUNT(*)
FROM
(SELECT MemberID, COUNT(*) AS ArticleViews FROM articleViews GROUP BY articleID, MemberID HAVING COUNT(*) >= 2) t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-21 : 01:58:13

quote:
Originally posted by jwallz

i have a table, 'articleViews' with columns
id, articleID, MemberID, viewDate

I need to get a total count of members who have viewed at least 2 distinct articles. Can anyone help? I've been on this for 2 hours.
It would be greatly appreciated.


SELECT COUNT(MemberID)
FROM
(
SELECT MemberID
FROM articleViews
GROUP BY MemberID
HAVING COUNT(DISTINCT articleID)>=2
)t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-21 : 02:04:44

quote:
Originally posted by tkizer

This might do it:

SELECT COUNT(*)
FROM
(SELECT MemberID, COUNT(*) AS ArticleViews FROM articleViews GROUP BY articleID, MemberID HAVING COUNT(*) >= 2) t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

if member has only viewed single article multiple times (same articleID different viewDate) , then this will still include the member

jwallz
Starting Member

14 Posts

Posted - 2009-02-21 : 12:20:02

thanks to both of you. I used the query from Visakh16. I needed the distinct. Seemed to be more accurate. I appreciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-22 : 01:05:41

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources