I'm trying to figure out how to do something in SQL and I'm stumped.
Essentially I have 5 tables
t_salesreps contains sales reps
t_cdusage contains session information - each time the sales rep uses the CD
t_contentusage contains 1 row for each presentation show in a session.
t_contentviewers contains doctors who viewed those presentations
t_doctors contains those doctors.
I need to count total viewers using an aggregate function. "Total
Viewers" is defined as the number of doctors who participate in a
session by viewing one or more presentations. If a doctor attends two
sessions, he gets counted twice - but *NOT* for each presentation.
The following query returns the count of doctors * presentations
select
count(t_doctors.pkey_doctor) as total_viewers_month
from
t_doctors A
inner join t_contentviewers B on A.pkey_doctor=B.pkey_doctor
inner join t_contentusage C on B.pkey_contentusage=C.pkey_contentusage
inner join t_cdusage D on C.pkey_cdusage=D.pkey_cdusage
inner join t_salesreps E on D.pkey_salesrep=E.pkey_salesrep
But I essentially need to return # of doctors * # of sessions.
If I do count(distinct t_doctors.pkey_doctor) it returns only "unique
viewers".. so doctors don't count twice if they attend two sessions.
What am I looking for here?
I'm going to experiment with some groupings and such... but if anyone
out there late on a sunday night wants to pipe in let me know!
Rick
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236800
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
