[SOLVED] How do I aggregate with group by in mysql withou setting sql mode?

How do I aggregate with group by in mysql withou setting sql mode?

This query gets the output I want. In order to run it I have to run

SET sql_mode = '';

Because otherwise I get an error:

SELECT list is not in GROUP BY clause and contains nonaggregated column 'knownLoss.t1.loss' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT 
  t1.klDate AS LDate
  , t1.Category
  , t1.reason AS Reason
  , sum(t1.loss) AS Loss
  , round(t1.loss / t2.loss,2) AS Percentage
FROM 
  deptkl t1
JOIN (
  SELECT 
      Store
    , sum(loss) AS loss
  FROM
    deptkl
  WHERE 
    klDate >= date_sub(SUBDATE(curdate(), WEEKDAY(curdate())), interval 7 day)
AND 
    klDate < SUBDATE(curdate(), WEEKDAY(curdate()))
AND
    Store = 19
AND
    Department = 80
) t2 ON t1.Store = t2.Store

WHERE 
  klDate >= date_sub(SUBDATE(curdate(), WEEKDAY(curdate())), interval 7 day)
  AND 
  klDate < SUBDATE(curdate(), WEEKDAY(curdate()))
  AND
  t1.Store = 19
AND
  Department = 80
GROUP BY
  klDate
, Category
, reason

When I place this into the Dataset and Query Dialog of Jasper Studio, I get the same error and I am unable to use the SET sql_mode = ''; command. Any thoughts? If there is a way to achieve this without using SET sql_mode = '';?

Solution

I think you want to do this :

round(sum(t1.loss / t2.loss)/count(*),2)  AS Percentage

this will calculate the sum of the average loss for every records in the result then divide it on the the count of the record of the group it's like average of average.

EDITS:

sorry i made a syntax error now ,it should give the th wanted result and the error is because you are not using aggregate function on a column that is not in group by clause