0

I have already asked a similar question, but I still don't seem to be able to get my head around it. I have a column device_create_date on my sql database

2015-07-14 12:35:19
2015-07-15 12:45:37
2015-07-15 12:45:37
2015-07-16 12:35:37
2015-08-14 10:35:21
2015-08-14 12:15:36

I am aggregating these date values in weeks, but there are missing values in between the weeks, where I want these values to just equal 0 so that I can compere them with other metrics in an excel sheet. I created a dummy table "date" to join the values with, so that the select returns 0 for the weeks there are no values for.

Here is the query I used,

select date.year, week(device.device_create_date), count(*)
from device 
join date
on date.week_of_year=week(device.device_create_date)
where device.id_customer = 1
group by device.year, date.WEEK_OF_YEAR
order by device.year, date.week_of_year;

Here is what I got back,

2010    26  7252
2010    31  3108
2010    45  7203
2010    1   9324
2010    2   7252
2010    3   2072

Here is what I want it to give me back;

2010 1  9324
2010 2  7252
2010 3  2072
2010 4  0
2010 5  0
2010 6  0 
etc

btw these values don't seem to be matching the count on the database either, so I kinda know I'm doing something completely wrong but I am not great at this so any help and direction would be greatly appreciated.

Thank you in advance!!

Nick Ko
  • 435
  • 4
  • 16

1 Answers1

1

Since you always want values from the date table (to get all possible dates) and the the matching rows from device you'll want to use date as the source and use a left join with the device table since an inner join would only return data that matches in both tables.

Try this instead:

select date.year, date.week_of_year, count(device.device_create_date) 
from date
left join device on date.week_of_year = week(device.device_create_date) 
                and device.id_customer = 1
group by date.year, date.week_of_year
order by date.year, date.week_of_year;

If your tables contain data from more than one year you should add a condition for the year to the join so you don't mix data from multiple years.

jpw
  • 44,361
  • 6
  • 66
  • 86
  • That worked wonders, thank you very much jpw its much appreciated, I spotted the mistake as well after your comment, instead of count(*), count the device create date. I'm under 15 rep so I can't vote up your comment but it was perfect! – Nick Ko Nov 05 '15 at 10:38
  • @NickKo If you have data for upcoming years in the `date` table you need to add a filter to only get the year you want: `where date.year = 2015` for instance. You should probably add `and date.year = year(device.device_create_date)` to the join too. – jpw Nov 05 '15 at 10:55
  • I read the last comment in answer a bit late hah, yeah it's all working fine now, thank you. #thumbsup – Nick Ko Nov 05 '15 at 11:09