Find Count of Existence of Row not Null within data set

I have a table called "VisitDetails" which tracks pages viewed within a Visit.

Looks like this:

VisitID     PageID     CategoryID      ProductID  (all are int, VisitID FK to a table called Visits which hold data about the visitor)

A Visit consists of multiple page views - they may be Pages, Categories, or Products.

I am trying to figure out how many Visits have a ProductID in any rows.  For example data:

VisitID     PageID     CategoryID      ProductID
1               NULL          NULL                100
2                1000          NULL                NULL
1              1000            NULL                NULL
1               NULL           10000               NULL
2              NULL             NULL                101

My ideal results would show me that 100% (count 2 of 2 unique Visits had a ProductID).  This will tell me what proportion of Visits viewed any Product.

Struggling to write an effective query to do this.
ccleebeltPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
I think a query like this will list all unique Visits with Products where the ProductID could either be populated or null:
SELECT v.VisitID, vd.ProductID
FROM Visits v LEFT JOIN 
(SELECT VisitID, ProductID FROM VisitDetails WHERE ProductID IS NOT NULL) vd
ON v.VisitID = vd.VisitID

Open in new window

From the query above it is not hard to calculate the proportion:
;with data as (
SELECT v.VisitID, vd.ProductID
FROM Visits v LEFT JOIN 
(SELECT VisitID, ProductID FROM VisitDetails WHERE ProductID IS NOT NULL) vd
ON v.VisitID = vd.VisitID),
counts as (
SELECT COUNT(VisitID) as v_cnt, SUM(CASE WHEN productID IS NOT NULL THEN 1 ELSE 0 END) as p_cnt
FROM data)
SELECT CAST(p_cnt AS FLOAT)/CAST(v_cnt AS FLOAT)*100 as proportion from counts

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
Would it be possible to have data like this?
VisitID     PageID     CategoryID      ProductID
 1               NULL          NULL                100
 2                1000          NULL                NULL
 1              1000            NULL                NULL
 1               NULL           10000               NULL
 2              NULL             NULL                101
 3              1000            NULL                NULL  ==> No product id at all
 1               NULL          NULL                101     ==> Repeat VisitID for 2 ProductIDs
ccleebeltPresidentAuthor Commented:
Yes, it is very likely that a product is viewed in multiple visits.

I added a distinct to the visit ID and inner join to what Chaau offered and it seems to work.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

awking00Information Technology SpecialistCommented:
I'm not sure where you inserted the distinct keyword, but I get some odd results when the data is like the possibility I presented
when using that query.

This query produced  a result of .66667 which would represent 2 visits with productids and 3 visitids -
select cast(sum(case when prodcnt = 0 then 0 else 1 end) as float)/cast(count(visitid) as float) as prodvisitratio from
(select visitid, count(productid) prodcnt
 from visitdetails
 group by visitid);

Note - I don't have sql server available to me so I didn't need to cast, but added it since I think it may be necessary.
awking00Information Technology SpecialistCommented:
The subquery needs an alias as well
(select visitid, count(productid) prodcnt
  from visitdetails
  group by visitid) v
awking00Information Technology SpecialistCommented:
Did you try both methods with those two records added to your sample? I'm not worried about any points, but I am curious about the results.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.