CODEWITHSUNDEEP
mysql
g00se0ne
g00se0ne

Reputation: 4620

Trying to get unique records with MySQL

I have a query that is looking at a 'page_views' table. And I so far have the ability to get the total views by day. However, I am having an issue with getting unique views by day.

The column I am testing against is user_id... Here's the query I have for total views.

SELECT 
  site_id, 
  CONCAT(  month(created_at) 
         , '-'
         ,   day(created_at)
         , '-' 
         , year(created_at)
        ) as created_at_date, 
  COUNT(*) as total_results 
FROM 
  page_views 
GROUP BY day(created_at)

I can't figure out the logic to just get the total unique views by day unique being based on the user_id column.

Schema:

id:int
user_id:int
site_id:int
request_uri:varchar
referer:varchar
user_agent:varchar
created_at:datetime

Upvotes: 0

Views: 386

Answers (2)

martin clayton
martin clayton

Reputation: 78115

Suggest you use the DATE_FORMAT function instead of CONCAT:

SELECT
    site_id,
    DATE_FORMAT( created_at, '%m-%d-%Y' ) AS created_at_date,
    COUNT( DISTINCT user_id ) AS unique_users
FROM
    page_views
GROUP BY
    site_id,
    DATE_FORMAT( created_at, '%m-%d-%Y' )

Note that (as xycf7 mentions) your posted query groups by day-of-month, which would show you at most 31 groups - which may not be what you intended.

Upvotes: 0

xycf7
xycf7

Reputation: 913

First, your original query is not correct. (it will return invalid results in the same days of different months or for different sites) It should be as belows


SELECT 
  site_id, 
  CONCAT(month(created_at) , '-',   day(created_at), '-' , year(created_at)) 
                                                         as created_at_date, 
  COUNT(*) as total_results 
FROM 
  page_views 
GROUP BY 
  site_id, 
  CONCAT(month(created_at) , '-',   day(created_at), '-' , year(created_at))

And for the original question, below query should work :


SELECT 
  site_id, 
  CONCAT(month(created_at) , '-',   day(created_at), '-' , year(created_at))
                                                           as created_at_date, 
  COUNT(distinct user_id) as unique_users
FROM 
  page_views 
GROUP BY 
  site_id, 
  CONCAT(month(created_at) , '-',   day(created_at), '-' , year(created_at))

Upvotes: 2

Related Questions