CODEWITHSUNDEEP
mysqlsql
dmohr
dmohr

Reputation: 2819

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

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

Upvotes: 1

Views: 2333

Answers (2)

Zach Victor
Zach Victor

Reputation: 469

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
;

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133400

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;

Upvotes: 0

Related Questions