MySQL: Show daily count of created rows with inner join?

April 17, 2023

I have a users and user_data table. I’m trying to show signups by created_at and group it by the count for that day, and show a record for each day, so I can see the increment of user sign ups.

user_data is optional, and isn’t always there (yet), but I only want to count rows which do have a user_data row.

I’m running this, but its just returning 1 row for each user sign up, 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

>Solution :

you need to group by the date portion of the
created_at
column

SELECT DATE(users.created_at) AS signup_date, COUNT(*) AS signup_count
FROM users
INNER JOIN user_data ON users.id = user_data.user_id
GROUP BY signup_date;