CODEWITHSUNDEEP
mysqlsqljoin
Th3lmuu90
Th3lmuu90

Reputation: 1717

Using joins in one table

I don't know yet how to use joins and whenever I try to use them, my queries won't work.

What I want to to is to show users that have signed up grouped by days.

TABLE USER

But what happens if there is a day where there are no sign ups? That day is not shown but I want it to be showed.

select COUNT(a.user_id) as count1, a.register_date as count2 
from user a 
left outer join user b on a.user_id = b.user_id 
GROUP BY a.register_date

I tried to adapt what I wanted from some examples but the previous query does not work since it does not show all the dates.

What I what is the following:

COUNT -> DATE

Upvotes: 0

Views: 55

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

The JOIN that you are doing is unnecessary, as it is not giving you any additional information than if you would do:

select COUNT(a.user_id) as count1,
  a.register_date
from user a
group by a.register_date

This should give you the number of users in every register_date that you have in your table. If there are no signups, something like this won't work.

You would have to feed it the dates somehow and then it would be appropriate to use a LEFT JOIN with the table USERS to get the COUNT. Having for example, a table with all the dates you want to query called dateTable you would do:

SELECT t1.date,COUNT(a.user_id)
FROM dateTable t1
LEFT JOIN users a ON t1.date = a.register_date
GROUP BY t1.date;

See this question for instructions on how to create a calendar table, or google sql calendar table on google. There are lots of useful information about this topic.

Upvotes: 3

Related Questions