I am trying to test a query in test environment which is as follows
Query : -
SELECT a.aggreatorid AS aggreatorid, '' AS txndesc, DATE_FORMAT(DATE(b.txndate),'%d/%m/%Y') AS txndate,
SUM(txncredit) AS txncredit, SUM(txndebit) AS txndebit
FROM walletmast a INNER JOIN wallettransaction b ON a.walletid=b.walletid
AND txndate BETWEEN CURDATE() - INTERVAL 3 DAY AND CURDATE()
GROUP BY a.aggreatorid, DATE(b.txndate)
ORDER BY txndate DESC
The query is throwing following
Error : -
SQL Error (1055): 'digitalpurse.b.txnDate' isn't in GROUP BY
The query works on my local environment but not on test environment after a lot of searching I found that the test environment has sql_modes which can not be changed for application flow and security purpose
SQL Mode : -
@@sql_mode |
+------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION |
Is there a way to run this query without changing the sql_mode? Let me know if any other information is requried. Thank you in advance :D
Move the query into a subquery that selects DATE(b.txndate)
, so that it matches the GROUP BY
clause, and then perform the formatting in the main query.
SELECT aggreatorid, '' AS txndesc, DATE_FORMAT(txndate, '%d/%m/%Y') AS txndate,
txncredit, txndebt
FROM (
SELECT a.aggreatorid AS aggreatorid, DATE(b.txndate) AS txndate,
SUM(txncredit) AS txncredit, SUM(txndebit) AS txndebit
FROM walletmast a
INNER JOIN wallettransaction b ON a.walletid=b.walletid
WHERE txndate BETWEEN CURDATE() - INTERVAL 3 DAY AND CURDATE()
GROUP BY a.aggreatorid, DATE(b.txndate)
) AS x
ORDER BY txndate DESC