Run this query without changing the sql_mode

Question

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

Possible duplicate of [Is there ANY\_VALUE capability for mysql 5.6?](http://stackoverflow.com/questions/37089347/is-there-any-value-capability-for-mysql-5-6) — e4c5, Jan 23 '17 at 07:58
@Strawberry did that, but the data returned on query is not right thus the need of posting this question — Abhishek Patil, Jan 23 '17 at 08:00
What version of MySQL are you using? If it's 5.7, you can use `ANY_VALUE()`. — Barmar, Jan 23 '17 at 08:01
Change the name of that alias in both the select and the group by — Strawberry, Jan 23 '17 at 08:03

score 1 · Accepted Answer · answered Jan 23 '17 at 08:05

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

score 1 · Answer 2 · answered Jan 23 '17 at 08:06

SELECT a.aggreatorid
     , '' txndesc
     , DATE_FORMAT(DATE(b.txndate),'%d/%m/%Y') dt
     , SUM(txncredit) txncredit
     , SUM(txndebit) txndebit  
  FROM  walletmast a 
  JOIN wallettransaction b 
    ON a.walletid=b.walletid  
  AND txndate  BETWEEN CURDATE() - INTERVAL 3 DAY AND CURDATE()  
   GROUP BY a.aggreatorid, dt, txndesc
   ORDER BY txndate DESC

Personally, I think it best that dates appear in the end users locale, so an exercise for JavaScript or PHP rather than MySQL

Run this query without changing the sql_mode

2 Answers2