title: SQL GROUP BY on a sub query
body: <p>I have a query that will return results from 2 tables into 1 using a UNION ALL, which all works as I need it to. However I need to run a GROUP BY and an ORDER BY on the returned dataset however I am getting many errors and I'm not sure how to solve it.</p>
  
  <p>Here is my Query:</p>
  
  <pre><code>SELECT ProductID, Quantity 
  FROM BasketItems 
  UNION ALL 
  SELECT ProductID, Quantity 
  FROM OrderItems
  </code></pre>
  
  <p>This will return a results set such as this:</p>
  
  <pre><code>ProductID  Quantity  
  15         2
  20         2
  15         1
  8          5
  5          1
  </code></pre>
  
  <p>I then want to run a <code>GROUP BY</code> on the <code>ProductID</code> field and then finally an <code>ORDER BY DESC</code> on the <code>Quantity</code> field. So in the final output, this particular results set will finally result in this:</p>
  
  <pre><code>ProductID
  8
  15
  20
  5
  </code></pre>
  
  <p>I can then run queries on this result set as I usually do</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>As stated above, but maybe not implied enough is that I will need to run queries on the returned results, which isn't working as you cannot run a query on a set of results that have an ORDER BY clause (so far as I gathered from the error list)</p>
  
  <p>If you want more information on the problem, here it is:</p>
  
  <p>From this results set, I want to get the products from the product table that they relate to</p>
  
  <pre><code>SELECT * FROM Products WHERE ID IN (
      SELECT ProductID
      FROM
      (
          SELECT ProductID, Quantity  
          FROM BasketItems  
          UNION ALL  
          SELECT ProductID, Quantity  
          FROM OrderItems 
      ) v
      GROUP BY ProductID
      ORDER BY SUM(Quantity) DESC
  ) 
  </code></pre>
  
  <p>However, I get this error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.</p>
  
  <p>The output of products need to be in the order that they are returned in the sub query (By quantity) </p>