Trying to get unique records with MySQL

Question

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

Answer 1

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))

Answer 2

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.