Question

MySQL

SELECT
   DWTIME.TM_MONTH AS TM_MONTH,
   DWSALESFACT.SALE_UNITS AS NUMPROD,
   SUM(DWSALESFACT.SALE_PRICE*DWSALESFACT.SALE_UNITS) AS TOTSALES
FROM DWSALESFACT INNER JOIN DWTIME
ON
DWSALESFACT.TM_ID=DWTIME.TM_ID
GROUP BY
DWTIME.TM_MONTH WITH ROLLUP;

TM_MONTH NUMPROD TOTSALES 9 23 1239.85 10 13 1012.21 NULL 36 2252.06

ERROR 1055 (42000) at line 2: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Ch13_SaleCo.DWSALESFACT.SALE_UNITS' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

What am i missing???

Answer 1

ANSWER

Hi this can be easly solve by changing the sql_mode value. Mysql server can operate different sql_mode. sql_mode is system varaiable. We can change with our operating requirments. We can see the values of our system sql_mode using following query

select @@sql_mode;

My system setting is as follows.

MariaDB [amith]> select @asql_mode; | casql_mode | STRICT_TRANS_TABLES , ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION | 1 row in set (0.000 sec)

For change this sql_mode at runtime, set the global or session sql_mode system variable using a SET statement

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

we can use the different modes in modes section. In the above figure shows the default modes.

To resolve your issue please set sql_mode with 'ONLY_FULL_GROUP_BY'

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

or

SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY';

Any doubts please comment and upvote too...

Thanks in advance