MySQL: Display daily count of rows created using inner join?

Question

I have a user and user_data table. I'm trying to display registrations by created_at and group them by today's count and display the records for each day so that I can see the delta in user registrations.

user_data is optional and is not always present (yet), but I only want to count rows that do have user_data rows.

I am running this but it only returns 1 row for each user registration, how can I group the dates?

SELECT COUNT(*), created_at, email 
FROM users 
INNER JOIN user_data  ON users.id = user_data.user_id
GROUP BY users.created_at, email

Answer 1

Based on your description, it seems you want to display user registration counts by date and group them accordingly. To better understand your requirements and provide more accurate guidance, e.g. provide sample tables and expected results.

This is the modified query:

SELECT DATE(u.created_at) AS signup_date, COUNT(*) AS signups
FROM users u
INNER JOIN user_data ud ON u.id = ud.user_id
GROUP BY signup_date
ORDER BY signup_date ASC;SELECT DATE(u.created_at) AS signup_date, COUNT(*) AS signups
FROM users u
INNER JOIN user_data ud ON u.id = ud.user_id
GROUP BY signup_date
ORDER BY signup_date ASC;

This will give you the registration count by date.