Is it possible to get sum without change sql_mode?

Question

I have a problem to get SUM value from multiple table using join statement. The error is:

this is incompatible with sql_mode=only_full_group_by

Is it possible to get sum without change sql_mode? If possible, how to make a SQL statement to?

Table fuel:

vehicle_id | liter
-----------+-----------
         2 | 43.5
         4 | 78.3
         8 | 20.5

Table usage:

date_usage | vehicle_id
-----------+-----------
2019-10-01 |  8
2019-10-15 |  2
2019-10-20 |  8
2019-10-20 |  4
2019-11-02 |  8

The SQL statement is below:

SELECT fuel.vehicle_id, SUM(fuel.liter), usage.date_usage
FROM fuel
LEFT JOIN usage ON fuel.vehicle_id = usage.vehicle_id
WHERE fuel.vehicle_id='8'
AND usage.date_usage >='2019-10-01' AND usage.date_usage <='2019-10-31'
GROUP BY fuel.vehicle_id

Answer 1

You have two possibilities to solve this:

• Remove column usage.date_usage from SELECT.
• Use a aggregate function on column usage.date_usage too:
  • MAX to get the highest value of the column.
  • MIN to get the lowest value of the column.
  • ANY_VALUE to get any value of the column.

So your query can look like the following (using MAX on column usage.date_usage):

SELECT fuel.vehicle_id, SUM(fuel.liter), MAX(`usage`.date_usage)
FROM fuel LEFT JOIN `usage` ON fuel.vehicle_id = `usage`.vehicle_id
WHERE fuel.vehicle_id = 8
  AND `usage`.date_usage BETWEEN '2019-10-01' AND '2019-10-31'
GROUP BY fuel.vehicle_id

demo on dbfiddle.uk

Note: be careful using words like usage as identifiers (e.g. table and column names) in MySQL. There are keywords and reserved words you should avoid or quote with backticks.