Unable to filter SQL data from last 30 days-CodePudding

I want to collect data from SQL from the last 30 days. This code shows data of all time

$result = $dbh->query('SELECT country,COUNT(*) FROM data_able GROUP BY country');

this shows nothing, instead of showing last 30 days data.

$result = $dbh->query('SELECT country,COUNT(*) FROM data_able GROUP BY country WHERE dtime > DATE_SUB(CURDATE(), INTERVAL 30 DAY)');

All SQL entries were made within the last 30 days. also tried

$result = $dbh->query('SELECT country,COUNT(*) FROM data_able WHERE dtime > DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY country');

What am I doing wrong here ? database dtime section

CodePudding user response：

Try this:

SELECT country, COUNT(*) 
FROM data_able 
WHERE dtime > DATE_ADD(CURDATE(), INTERVAL -30 DAY)
GROUP BY country

The second doesn't work because you put GROUP BY before WHERE statement, which is not correct SQL order.

As for why the third code doesn't work, I'm not sure, but if I had to guess, it has something to do with that DATE_SUB statement.