Using joins in one table

Question

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

user_id
register_date

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

1 ------- 01-01-2013
0 ------- 02-01-2013
5 ------- 03-01-2013
0 ------- 04-01-2013
0 ------- 05-01-2013

That can happen if my website had zero sign ups on that particular day, simple. — Th3lmuu90, Dec 12 '13 at 19:57
Do you mean you want a date to show up but with a count of 0? — Jack Marchetti, Dec 12 '13 at 19:57
you need a calendar table if you want to do that as @Loc said — Sam, Dec 12 '13 at 19:58
@Loc. He wants all dates, and the ones that have no registrations, he wants 0 on those days. — Filipe Silva, Dec 12 '13 at 20:02
Joining users to users with the user_id on both ends won't accomplish anything. Each row will be matched up with itself. — Ian McLaird, Dec 12 '13 at 20:02

score 3 · Accepted Answer · edited May 23 '17 at 12:19

3

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.

edited May 23 '17 at 12:19

Community

1
1

answered Dec 12 '13 at 19:53

Filipe Silva

21,189
5
53
68

1

Now that I've thinked twice, it is impossible to do what I want since there will be no date for that day, since no records were created with that date. – Th3lmuu90 Dec 12 '13 at 20:05
1

@Th3lmuu90. Yes, by using users twice wouldn't accomplish it. That's why you need to use a calendarTable – Filipe Silva Dec 12 '13 at 20:06
What about this query? "select COUNT(user_id),register_date FROM user WHERE register_date BETWEEN CURDATE()-7 AND CURDATE() GROUP BY register_date" I still believe that it can be achieved since I give a date range so it will search for THAT date even if it does not exists on the table. – Th3lmuu90 Dec 12 '13 at 20:14
No. All data in the result of a query *must* come from the table(s) being queried, or be supplied in the `SELECT` clause (such as constant values or calculations). Date ranges in the query itself do not matter. You *need* a table for the data to come from. – Ian McLaird Dec 12 '13 at 20:15
That's true? Well, seems like that I need to forgot about using only a table. – Th3lmuu90 Dec 12 '13 at 20:18

Using joins in one table

1 Answers1