How Can I Count Distinct Site Visits and Users in MySQL?

Distinct Counts with MySQL's "COUNT DISTINCT"

When working with database data, it's often necessary to understand the count of distinct values for certain attributes. In MySQL, the "COUNT DISTINCT" function allows you to accomplish this.

Consider a scenario where you need to determine the number of distinct site visits to your website yesterday. Using the following query:

SELECT
    DISTINCT `user_id` as user,
    `site_id` as site,
    `ts` as time
FROM
    `cp_visits`
WHERE
    ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)

However, this query returns multiple results with the same site_id. To obtain the count of distinct site logins alone, you need to modify your query:

Select
     Count(Distinct user_id) As countUsers
   , Count(site_id) As countVisits
   , site_id As site
 From cp_visits
 Where ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)
 Group By site_id

Explanation:

• The Count(Distinct user_id) and Count(site_id) functions calculate the distinct count of user IDs and site IDs, respectively.
• The Group By site_id clause groups the results by the site ID. This ensures that only one record is returned for each distinct site ID.

The results of this query will provide the count of distinct users and visits for each site that was visited yesterday.

The above is the detailed content of How Can I Count Distinct Site Visits and Users in MySQL?. For more information, please follow other related articles on the PHP Chinese website!