mysql return distinct values from one column, group by another column with count

Question

I have a view called 'videos' with the following columns:

email(varchar)
time(varchar)
page(varchar)

The view has rows for every time a person with a given email visited a page.

I want to return results that show for each email address, a count of how many days they visited at least one page, and a count of how many distinct pages they visited regardless of date.

One of my issues is probably that the time is stored as a varchar. Here is an example of the format the time is in.

2016-01-25T14:36

Zach Victor · Accepted Answer · 2016-08-18 20:13:35Z

3

Use STR_TO_DATE to extract and interpret the date-portion of your time string. Use COUNT with the DISTINCT keyword to count only distinct dates and distinct pages.

SELECT email
     , COUNT(DISTINCT STR_TO_DATE(time, '%Y-%m-%d')) AS visit_days
     , COUNT(DISTINCT page) AS visit_pages
  FROM videos
 GROUP BY email
;

answered Aug 18, 2016 at 20:13

Zach Victor

4893 silver badges7 bronze badges

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

1 Comment

dmohr Over a year ago

That works perfectly, thanks! I wasn't sure how to do two separate counts in a single query

ScaisEdge · Accepted Answer · 2016-08-18 18:57:17Z

0

You can use a group by fro the count of the visit by day

select email,  date(time), count(*)
from mytable 
group by email, date(time);

and

select email,   count(distinct page)
from mytable 
group by email;

answered Aug 18, 2016 at 18:57

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

mysql return distinct values from one column, group by another column with count

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related