title: Writing a specific SQL Group By Statement
body: <p>I have a table which I have screenshotted and uploaded the image here:</p>
  
  <p><img src="https://i.stack.imgur.com/zqagH.png" alt="enter image description here"></p>
  
  <p>I need to create an sql query that counts the number of orders with the following pizza quantities: 1, 2, 3, 4, 5, 6 and above 6. </p>
  
  <p>I've tried a number of SQL statements but haven't found the right one. </p>
  
  <p>My current attempt is </p>
  
  <pre><code>&lt;asp:SqlDataSource ID="SqlDataSource1" runat="server" 
              ConnectionString="&lt;%$ ConnectionStrings:ConnectPizza %&gt;" 
              SelectCommand="Select '1' AS Caption, Count(quantity) AS Count FROM orders WHERE quantity = 1 
              UNION ALL
              Select '2' AS Caption, Count(quantity) AS Count FROM orders WHERE quantity = 2
              UNION ALL
              Select '3' AS Caption, Count(quantity) AS Count FROM orders WHERE quantity = 3
              UNION ALL
              Select '4' AS Caption, Count(quantity) AS Count FROM orders WHERE quantity = 4
              UNION ALL
              Select '5' AS Caption, Count(quantity) AS Count FROM orders WHERE quantity = 5
              UNION ALL
              Select '6' AS Caption, Count(quantity) AS Count FROM orders WHERE quantity = 6
              UNION ALL
              Select 'Above 7' AS Caption, Count(quantity) AS Count FROM orders WHERE quantity &gt; 7"&gt;
          &lt;/asp:SqlDataSource&gt;  
  </code></pre>
  
  <p>but it isn't going successful. </p>
  
  <p>I want to make a chart too, but even if my sql statement were successful I'm not sure what I'd enter for my X and Y value members.</p>
  
  <p>Any help would be appreciated.</p>