How to count number of dates base on usage

can I ask some help, how to count the number of dates that are only consumed ?
example I have this data in my table

usage_date

2015-09-02 12:00:37
2015-09-02 12:02:37
2015-09-02 12:05:37
2015-09-02 12:09:37
2015-09-03 03:02:37
2015-09-03 03:45:37
2015-09-05 18:29:37

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()

how can I count to get it 3days ?

Thank you in advance.

SELECT * 
FROM table 
WHERE DATEDIFF(NOW(), usage_date) <= 30 
GROUP BY YEAR(usage_date), MONTH(usage_date), DAY(usage_date)

Number of selected rows will be the value you’re looking for

I’m sure there should be more efficient solution, but this one works too

the dreaded evil “select star” used along with a GROUP BY clause

i just threw up in my mouth a little

@jemz do you have an integers or number table?

if not, you need one http://www.xaprb.com/blog/2005/12/07/the-integers-table/

you mean for auto increment id ?yes I have .

you do? that’s great!

just to confirm, could you please do a SHOW CREATE TABLE on your numbers table please

Here is the result.

CREATE TABLE usage_lines_table (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
devid varchar(15) COLLATE utf8_unicode_ci NOT NULL,
rdg_value int(11) NOT NULL,
type_id int(11) NOT NULL,
usage_header_id int(10) unsigned DEFAULT NULL,
usage_date datetime NOT NULL,
PRIMARY KEY (id)

) ENGINE=InnoDB AUTO_INCREMENT=2477 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

that’s not a numbers table

this is what you need –

CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY ) ; INSERT INTO numbers VALUES ( 0),( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9) ,(10),(11),(12),(13),(14),(15),(16),(17),(18),(19) ,(20),(21),(22),(23),(24),(25),(26),(27),(28),(29) ,(30) ;
let me know when you’ve got this ready

you can load it with more integers, but you’ll need at least 31 numbers to select a month’s worth of dates

I have it now with numbers 0-31

CREATE TABLE `numbers` (
  `n` int(11) NOT NULL,
  PRIMARY KEY (`n`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

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

Yes, I really don’t understand the query. can you please enlighten me.

please run it and comment on the results you get

then i’ll explain it if you still don’t understand

you know what an OUTER JOIN does, right?

@r937

Is this correct ON DATE(usage_lines_table) ?
or should be ON DATE(usage_date)

because I get error running the code.

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).

1 Like

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 ?

well done, yes, that was an error on my part

(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

did your test of the query reveal any dates with 0 usage counts?

this is the result
I add filter

 type_id = 2 

after the where clause.
but if I will not add type_id , it shows date with zero count.

you need to add it to the ON clause instead

I am confuse how to add the type_id =2 to the on clause.

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