title: MySQL GROUP BY not working as expected
body: <p>I have written a series of nested procedures which perform some pay calculations for employees. Throughout these procedures data is stored in temp tables.  </p>
  
  <p>Now the issue is some unexpected behaviour when I perform a GROUP BY on one of these temp tables. You can see an example of that step here:
  <a href="http://www.sqlfiddle.com/#!9/f4095/2" rel="nofollow noreferrer">http://www.sqlfiddle.com/#!9/f4095/2</a></p>
  
  <p>The expected behaviour is for that GROUP BY to output a list of <code>employeeid</code> and the corresponding aggregations. The sqlfiddle works as expected. However, what is happening is that I am getting a single row in the output with a blank <code>employeeid</code> and aggregated values for all rows. However, if I filter the data in a previous step to include a single unique <code>employeeid</code>, then I get a single row in the output (as expected) and with the <code>employeeid</code> column containing a value instead of being blank.</p>
  
  <p>Initially, I thought this could be a memory issue since I am using lots of temporary tables (my understanding is that these are stored in memory) and wasn't dropping them. So I modified all of the procedures to drop any temporary tables as soon as they were no longer required. I am still facing the same issue. My tables are about 2000 rows and around 100 columns and the output of the GROUP BY should only be about 300 rows and 7 columns.</p>
  
  <p>I'm going to continue thinking of different ways I can test this and will update if I find anything, but my question is: Does anyone know of anything that could potentially cause this type of behaviour?</p>
  
  <p>EDIT: I've provided a simplified version in the sqlfiddle above, but here is the statement causing the problem (the table <code>temp_rawpaycalc</code> which is being GROUPED contains <code>shift_id</code>, <code>employeeid</code>, and all of the columns which are being aggregated. <code>employeeid</code> is in the <code>SELECT</code> and the <code>GROUP BY</code>, and <code>shift_id</code> is excluded from both.</p>
  
  <p><code>DROP TEMPORARY TABLE IF EXISTS temp_topupaggr;
  CREATE TEMPORARY TABLE temp_topupaggr
  SELECT employeeid,
      SUM(hoursworked) AS TotalHours,
      SUM(minguaranteepayamount) AS TotalMinGuarantee,
      (
          SUM(baseWagePayAmount) +
          SUM(basicAppPayAmount) +
          ...
          SUM(suppPayAmount)) AS TotalEarnings,
      SUM(reported_hoursworked) AS reported_TotalHours,
      SUM(reported_minGuaranteePayAmount) AS reported_TotalMinGuarantee,
      (
          SUM(reported_baseWagePayAmount) +
          SUM(reported_basicAppPayAmount) +
          ...
          SUM(reported_suppPayAmount)) AS reported_TotalEarnings
  FROM temp_rawpaycalc 
  GROUP BY employeeid;</code></p>