MySQL Using SUM in a JOIN giving? [duplicate]

Question

I am trying to get the SUM of a column in my MySQL query

SELECT pi.*
     , pr.EK2
     , pr.EK3
     , SUM(P_OrderTotal) as SUM_P_OrderTotal 
  FROM pixi pi 
  LEFT 
  JOIN konditionen pr 
    ON pi.P_EAN = pr.EAN 
 WHERE pi.P_OrderDate >= '2021-03-01' 
   AND pi.P_OrderDate <= '2021-03-31';

This gives me:

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'table.pi.P_OrderNr'; this is incompatible with sql_mode=only_full_group_by

The query runs fine without the SUM(P_OrderTotal) as SUM_P_OrderTotal part. How would I solve this?

Answer 1

You must use a group by statement as the error message says. The GROUP BY statement is often used with aggregate functions (COUNT(), SUM(), AVG()) to group the result-set by one or more columns.

Answer 2

This will return 1 row due to aggregate which means the non-aggregate values are "random". Use a sub-query possible via common table expression:

SELECT pi.*
     , pr.EK2
     , pr.EK3
     , (select sum(P_OrderTotal) from ...) as SUM_P_OrderTotal
...