title: Group By and Order By with UNION ALL
body: <p>I have a stored procedure with the following query:</p>
  
  <pre><code>SELECT (sum(addition)) AS [COUNT], 
  MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
  FROM [TABLE_ONE]
  WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
  AND [ServiceID]=@serid
  GROUP BY CONVERT(VARCHAR(12),CREATED,102) 
  ORDER BY  CONVERT(VARCHAR(12),CREATED,102)  
  </code></pre>
  
  <p>I need to do a <code>union all</code>, so I could get sum of results but <strong>from TWO tables</strong>, and I want the result to be grouped by and ordered by the same way.</p>
  
  <p>This doesn't work:</p>
  
  <pre><code>SELECT (sum(addition)) AS [COUNT], 
  MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
  FROM [TABLE_ONE]
  WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
  AND [ServiceID]=@serid
  GROUP BY CONVERT(VARCHAR(12),CREATED,102) 
  ORDER BY  CONVERT(VARCHAR(12),CREATED,102)  
  
  UNION ALL
  
  SELECT (sum(addition)) AS [COUNT], 
  MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date]
  FROM [TABLE_TWO]
  WHERE convert(VARCHAR(12),CREATED,102) BETWEEN CONVERT(date,@startdate) AND CONVERT(date,@enddate)
  AND [ServiceID]=@serid
  GROUP BY CONVERT(VARCHAR(12),CREATED,102) 
  ORDER BY  CONVERT(VARCHAR(12),CREATED,102) 
  </code></pre>
  
  <p>I want to order the overall result, and group it by the date. </p>