MySQL: Get Unique Count from Multiple Columns

Question

SELECT numTimesIndexLoaded, numTimesIndexLoadedUnique, numTimesResultsLoaded, numTimesResultsLoadedUnique, numTimesCTALoaded, numTimesCTALoadedUnique
FROM
(SELECT
sum(ctaSL.page = 'index') AS numTimesIndexLoaded,
sum(ctaSL.page = 'results') AS numTimesResultsLoaded,
sum(ctaSL.page = 'cta') AS numTimesCTALoaded,
count(distinct ctaSL.ip_address, ctaSL.page = 'index') AS numTimesIndexLoadedUnique,
count(distinct ctaSL.ip_address, ctaSL.page = 'results') AS numTimesResultsLoadedUnique,
count(distinct ctaSL.ip_address, ctaSL.page = 'cta') AS numTimesCTALoadedUnique
FROM
stats_page_loads AS ctaSL
WHERE ctaSL.campaign_id = ?
    AND ctaSL.mode = ?
) t1

I have the following prepared statement where I'm trying to get stats for a web page. The goal is to have both the stats of how many times each of the three pages is load, but also how many times it's loaded by a unique IP address for each of the three pages. I'm new to using distinct, so not even sure if it's the right thing to use, but I'm not getting the proper result so I'm assuming I'm using it wrong.

I figured I could just make another SELECT statement as t2, but trying to avoid that since I'll have to add the ? variables over and over again the more I add to it, so was hoping to have it condensed into one, if possible.

See the following guidance how to ask a good sql related question: meta.stackoverflow.com/questions/333952/… This question as it stansd is unanswerable because the key is within the data. — Shadow
– Shadow, Commented Jan 17, 2017 at 20:49

Barmar · Accepted Answer · 2017-01-17 21:07:41Z

1

Try:

count(DISTINCT CASE WHEN ctaSL.page = 'index' THEN ctaSL.ip_address END) AS numTimesIndexLoadedUnique,

and similarly for the other cases. This works because CASE returns NULL if none of the WHEN clauses are matched, and COUNT doesn't count NULL.

Your attempt didn't work because the tests simply return 0 or 1 depending on whether they succeed, and you're just counting all the distinct combinations of the IP and that boolean. It doesn't exclude the rows where the condition fails.

answered Jan 17, 2017 at 21:07

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Damian Cardona Over a year ago

This worked perfectly, and thank you for the explanation. Very helpful.

Collectives™ on Stack Overflow

MySQL: Get Unique Count from Multiple Columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related