that dates is the day of usage, as you can see there is no 2015-09-04 because
theres is no usage,so the the total of days only is 3 days. those date
is query from last 30 days up to now()
let me know if you don’t understand what this is doing –
SELECT CURRENT_DATE + INTERVAL -n DAY AS the_date
, COUNT(usage_lines_table.id) AS the_count
FROM numbers
LEFT OUTER
JOIN usage_lines_table
ON DATE(usage_lines_table) =
CURRENT_DATE + INTERVAL -n DAY
WHERE n BETWEEN 0 AND 30
GROUP
BY the_date
Okay, guys, let’s bring this back to on topic. I originally read Rudy’s post as humorous myself, probably because I’ve been there before, however, having a nitty gritty back and forth on a topic that hasn’t quite reached its solution yet is problematic. Let’s actually focus on the problem at hand and let go over the lost humor or poor humor (however you choose to see it).
I’ll probably go through and do some cleanup of this topic here in a bit (as to not take away from the actual point of this topic).
I see now the output. it grouped by date and it count how many date repeated with same date.
How do i read this CURRENT_DATE + INTERVAL -n DAY
what is -n ?
(sorry, there has been a lot of distraction in this thread this morning)
as for -n it is a negative number
basically the numbers table is generating each of the last 30 dates, and then the dates are used in the LEFT OUTER JOIN so that your usage stats can be counted by each date
SELECT CURRENT_DATE + INTERVAL -n DAY AS the_date
, COUNT(usage_lines_table.id) AS the_count
FROM numbers
LEFT OUTER
JOIN usage_lines_table
ON DATE(usage_lines_table.usage_date) =
CURRENT_DATE + INTERVAL -n DAY
AND usage_lines_table.type_id = 2 -- here you go
WHERE n BETWEEN 0 AND 30
GROUP
BY the_date