Group By Error In MySQL with sql_mode=only_full_group_by

Question

Tring the following query but i get; Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'PDC.PLG.LogDateTime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I know that this is because of only full group by mode; how can I refactor this sort of query?

SELECT     
SUM(PLG.Qty) AS TotQty,
SUM(PLG.ScrapQty) AS ScrpQty,   
(
SELECT SUM(PLL.Qty) 
FROM ProductionLog AS PLL
INNER JOIN ProductionPlan PPP ON PPP.PlanId = PLL.PlanId
WHERE 
DATE(LogDateTime) = DATE(PLG.LogDateTime) AND 
LogType = 8 AND 
PPP.StationId = PP.StationId

) AS RwrkQty,

DATE(PLG.LogDateTime) AS LogDate,
S.StationName
FROM ProductionLog AS PLG
INNER JOIN ProductionPlan  AS PP ON PLG.PlanId = PP.PlanId
INNER JOIN Station AS S ON S.StationId = PP.StationId
WHERE PLG.Logtype IN (4)

GROUP BY S.StationId,DATE(PLG.LogDateTime)

Your query is in a mess, to the point where I can't even suggest anything. Maybe add some sample data to your question, along with the expected output. — Tim Biegeleisen
From the description of the tsql tag: "... Do not use this tag for MySQL, ...". I'm replacing it with sql which I guess is what you actually wanted. — sticky bit
What sub query does is; it reiterates the same table with different log types and sums it up grouped by per day. — Sin5k4

DRapp DRapp · Accepted Answer · 2019-04-07T15:00:35

First, a proper query using a GROUP By means whatever the result fields you are trying to return, your GROUP by should include ALL fields that do not have any aggregation applied (min, max, sum, avg, etc.) So what is missing is that an extra column is in the list that is not aggregated, but also not part of the group by. So either add this non-aggregate field to the group by (even if the last field in group by), OR apply some aggregation to it.

Now, a cleanup for readability of your original query for readability of what is where and/or subquery of the next.

SELECT     
      SUM(PLG.Qty) AS TotQty,
      SUM(PLG.ScrapQty) AS ScrpQty,   
      ( SELECT SUM(PLL.Qty)
           FROM ProductionLog AS PLL
              INNER JOIN ProductionPlan PPP 
                 ON PPP.PlanId = PLL.PlanId
           WHERE 
                  DATE(LogDateTime) = DATE(PLG.LogDateTime) 
              AND LogType = 8 
              AND PPP.StationId = PP.StationId ) AS RwrkQty,
      DATE(PLG.LogDateTime) AS LogDate,
      S.StationName
   FROM 
      ProductionLog AS PLG
         INNER JOIN ProductionPlan  AS PP 
            ON PLG.PlanId = PP.PlanId
         INNER JOIN Station AS S 
            ON S.StationId = PP.StationId
   WHERE 
      PLG.Logtype IN (4)
   GROUP BY 
      S.StationId,
      DATE(PLG.LogDateTime)

In your scenario, your 3rd column which is based on a query has already been aggregated INSIDE it, but for respect to the OUTER query, it is NOT aggregated. To simplify this, just wrap IT in a MIN() such as

      MIN( ( SELECT SUM(PLL.Qty)
           FROM ProductionLog AS PLL
              INNER JOIN ProductionPlan PPP 
                 ON PPP.PlanId = PLL.PlanId
           WHERE 
                  DATE(LogDateTime) = DATE(PLG.LogDateTime) 
              AND LogType = 8 
              AND PPP.StationId = PP.StationId ) ) AS RwrkQty,

Since the inner query is only ever returning 1 row, summing 1 row will return the same value anyhow.

Group By Error In MySQL with sql_mode=only_full_group_by

1 Answers