MySQL Using SUM in a JOIN giving?

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?

'How would I solve this?' - hard to say , you haven't told us what you are trying to achieve or provided sample data and desired output. and it's not obvious where p_order_total comes from. — P.Salmon, Mar 22 '21 at 07:41
Does this answer your question? [SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql\_mode=only\_full\_group\_by](https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc) (you could have found this yourself via: https://stackoverflow.com/search?q=sql_mode%3Donly_full_group_by ) — Luuk, Mar 22 '21 at 07:42

score 0 · Answer 1 · answered Mar 22 '21 at 07:41

0

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.

answered Mar 22 '21 at 07:41

Omegon

101
3

score 0 · Answer 2 · answered Mar 22 '21 at 07:46

0

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
...

answered Mar 22 '21 at 07:46

Allan Wind

11,844
4
24
32

MySQL Using SUM in a JOIN giving?

2 Answers2