Why do I get a syntax error on this query? I need some help with this query:

$result = db_query("SELECT
  UNIX_TIMESTAMP(FROM_UNIXTIME(s.timestamp, '%Y%m%d')) AS date,
  s.nid, COUNT(*) AS all_count,
  COUNT(DISTINCT s.ip_address) AS unique_count
FROM {simpleads_impressions} s
WHERE FROM_UNIXTIME(s.timestamp, '%Y-%m-%d') BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() - INTERVAL 1 SECOND
GROUP BY date, s.nid");


The main issue is the WHERE clause, and the count/group issue.

$query = \Drupal::database()
    -&gt;select('simpleads_impressions', 's')
    -&gt;fields('s', array('nid'));
$query-&gt;addExpression("UNIX_TIMESTAMP(FROM_UNIXTIME(timestamp, '%Y%m%d'))", 'date');
$query-&gt;addExpression('COUNT(*)', 'all_count');
$query-&gt;addExpression('COUNT(DISTINCT ip_address', 'unique_count');
$query-&gt;where("FROM_UNIXTIME(timestamp, '%Y-%m-%d') BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() - INTERVAL 1 SECOND");
$query-&gt;groupBy('date');
$query-&gt;groupBy('nid');
$result = $query-&gt;execute()-&gt;fetchObject();


This is the error message I get:


  Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS unique_count FROM simpleads_impressions s WHERE (FROM_UNIXTIME(timestamp, '' at line 1: SELECT s.nid AS nid, FROM_UNIXTIME(timestamp, '%Y-%m-%d') AS utime, DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS startdate, DATE_SUB(CURDATE(), INTERVAL 1 SECOND) AS enddate, UNIX_TIMESTAMP(FROM_UNIXTIME(timestamp, '%Y%m%d')) AS testdate, COUNT(*) AS all_count, COUNT(DISTINCT ip_address AS unique_count FROM {simpleads_impressions} s WHERE (FROM_UNIXTIME(timestamp, '%Y-%m-%d') BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() - INTERVAL 1 SECOND) GROUP BY date, nid; Array ( ) in simpleads_cron() (line 23 of D:\www\nla-refactor\modules\custom\simpleads\simpleads.module).


What code should I use to fix the error?
I changed the code to the following one, and I don't get the error anymore.

$query = \Drupal::database()
    -&gt;select('simpleads_impressions', 's')
    -&gt;fields('s', array('nid'));
$query-&gt;addExpression("FROM_UNIXTIME(timestamp, '%Y-%m-%d')", 'utime');
$query-&gt;addExpression("UNIX_TIMESTAMP(FROM_UNIXTIME(timestamp, '%Y%m%d'))", 'date');
$query-&gt;addExpression('COUNT(*)', 'all_count');
$query-&gt;addExpression('COUNT(DISTINCT ip_address)', 'unique_count');
$query-&gt;where("FROM_UNIXTIME(timestamp, '%Y-%m-%d') BETWEEN CURDATE() - INTERVAL 1 DAY AND CURDATE() - INTERVAL 1 SECOND");
$query-&gt;groupBy('date');
$query-&gt;groupBy('nid');
$query-&gt;groupBy('timestamp');
$result = $query-&gt;execute()-&gt;fetchObject();