CODEWITHSUNDEEP
mysqlmariadb
razn
razn

Reputation: 69

MySQL query runs successfully on local but gives DATE_FORMAT error on SERVER

I've this MySQL / MariaDB query:

SELECT
   a.id as agent_id
 , a.full_name
 , a.email
 , a.phone
 , a.location
 , COUNT(cs.agent_id) AS tot_subscribers
 , (SELECT
      IFNULL(SUM(COUNT(cs.id)), 0) 
    WHERE
      DATE_FORMAT(cs.transaction_id, '%c') = 3
   ) AS month_subscribers
 FROM
   agents a
 LEFT JOIN
   cream_sales cs
 ON
   a.id = cs.agent_id
 GROUP BY
   a.id

It runs successfully on my localhost but it gives the following DATE_FORMAT error when running on the server:

#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 'WHERE DATE_FORMAT(cs.transaction_id, '%c') = 3) AS month_subscribers FROM agents' at line 1

Local DBMS details:

Server type: MySQL
Server version: 5.7.21-0ubuntu0.16.04.1 - (Ubuntu)
Protocol version: 10
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $

Server DBMS Details:

Server type: MariaDB
Server version: 10.2.13-MariaDB - MariaDB Server
Protocol version: 10 
cpsrvd 11.68.0.33
Database client version: libmysql - 5.1.73
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation

Can anyone please help me find whats causing the issue. Thanks in advance.

Upvotes: 0

Views: 64

Answers (2)

Rick James
Rick James

Reputation: 142306

SUM(COUNT(...)) does not make sense. Don't nest aggregate functions.

Perhaps this is what you need for that one column:

( SELECT COUNT(id) FROM cs
      WHERE MONTH(transaction_id) = 3
) AS month_subscribers

Notes:

  • COUNT(x) checks x for being not null.
  • MONTH() is simpler (but not any more efficient).

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175786

I would use EXTRACT(MONTH FROM ... )/MONTH instead of relying on formatted data:

SELECT a.id as agent_id,
      a.full_name,
      a.email, a.phone, a.location, COUNT(cs.agent_id) AS tot_subscribers,
     (SELECT IFNULL(SUM(COUNT(cs.id)), 0)
      WHERE  MONTH(cs.transaction_id) = 3) AS month_subscribers 
FROM agents a 
LEFT JOIN cream_sales cs 
  ON a.id = cs.agent_id 
 GROUP BY a.id;

EDIT:

There is no need for subquery. Conditional aggregation will do the same:

SELECT a.id as agent_id,
      a.full_name,
      a.email, a.phone, a.location, COUNT(cs.agent_id) AS tot_subscribers,
      SUM(COUNT(CASE WHEN MONTH(cs.transaction_id) = 3 
                     THEN cs.id END)) AS month_subscribers 
FROM agents a 
LEFT JOIN cream_sales cs 
  ON a.id = cs.agent_id 
 GROUP BY a.id, a.full_name, a.email, a.phone, a.location;

Upvotes: 1

Related Questions