CODEWITHSUNDEEP
mysql
TharsDimitris
TharsDimitris

Reputation: 154

Query to Count Past and Future rows from mysql table

CREATE TABLE reminders` ( 
    id int(11) NOT NULL AUTO_INCREMENT, 
    url varchar(250) DEFAULT NULL, 
    type int(11) DEFAULT NULL, 
    actiondate datetime DEFAULT NULL, 
    PRIMARY KEY (id), UNIQUE KEY id_UNIQUE (id) ) ENGINE=InnoDB AUTO_INCREMENT=186 DEFAULT CHARSET=latin1;

I have a mysql table which lists all tasks of my team. Each row has a timestamp column. I want to count the number of tasks that have a past date

The actiondate contains values of the form: 

2017-12-06 00:00:00 
2017-12-06 00:00:00 
2017-03-06 00:00:00 
2017-12-06 00:00:00
2017-06-04 00:00:00

this is the column which contains the data i want to check against todays date

use =< CURDATE()

and the number of tasks that have a future day in one query.

use > CURDATE()

Doing some research it seems that CASE function of mysql might be able to help me with that but have not managed to make it work with the expected results so far.

Thanks for any help.

Upvotes: 0

Views: 129

Answers (2)

McNets
McNets

Reputation: 10807

Try it now:

select
    curdate() as 'Current date',
    sum(if(actiondate <= curdate(), 1, 0)) as tasks_before,
    sum(if(actiondate > curdate(), 1, 0)) as tasks_after
from
    reminders;

The result is:

Current date         tasks_before  tasks_after
-------------------  ------------  ----------- 
31.01.2017 00:00:00      6              4

I've set up an rextester example here: http://rextester.com/WXNU86748

Upvotes: 2

Ike Walker
Ike Walker

Reputation: 65537

You could use case, but for an even simpler query you can use your boolean expression to generate a 1/true for rows in the past, and a 0/false for rows in the future, then group by that value:

select (`use` =< current_date()) as in_future, 
  count(*)
from your_table 
group by in_future

Upvotes: 0

Related Questions