Find Count of Existence of Row not Null within data set

ccleebelt
ccleebelt used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2013
Commented:
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

awking00Information Technology Specialist

Commented:
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
ccleebeltPresident

Author

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

awking00Information Technology Specialist

Commented:
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 Specialist

Commented:
The subquery needs an alias as well
(select visitid, count(productid) prodcnt
  from visitdetails
  group by visitid) v
awking00Information Technology Specialist

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial