title: Use CTE and UNION ALL with SQL Server 2014
body: <p>My problem is that:</p>
  
  <p>Create a view that shows the top 5 selling products as well as an aggregated row that shows the total sales for all other products and a Grand total row that sums all of the above.</p>
  
  <pre><code>WITH ProductTop5 AS 
  (
      SELECT [dbo].[Product].[ProductName] AS ProductName, SUM([dbo].[SalesOrderDetail].[LineTotal]) AS TotalAmount
      FROM [dbo].[Product]
      JOIN [dbo].[SalesOrderDetail] ON [dbo].[Product].[ProductID] = [dbo].[SalesOrderDetail].[ProductID]
      GROUP BY [dbo].[Product].[ProductName]  
  )
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/NSQmv.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NSQmv.jpg" alt="enter image description here"></a></p>