title: GROUP BY Conditions
body: <p>For example I have a table with orders. The table has a key (Order.No), a date where the order was opened (Order.Open) and a date where the order was closed (Order.Close).</p>
  
  <pre>
  No          Open        Close
  ---------   ----------  ----------
  2013-1208   2013-03-11  2013-03-26
  2013-1272   2013-03-11  2013-03-11
  2013-1273   2013-03-11  2013-03-11
  2013-1274   2013-03-11  2013-03-11
  2013-1275   2013-03-11  2013-03-11
  2013-1280   2013-03-11  2013-06-26
  2013-1281   2013-03-11  2013-04-18
  2013-1282   2013-03-11  2013-03-14
  2013-1287   2013-03-12  2013-04-18
  2013-1291   2013-03-12  2013-03-12
  </pre>
  
  <p>Now I want to make a query, where I can find out for each month how many orders were still open on the last day of the month. </p>
  
  <p>For example I want to find out how many orders were still open on the last day of January:</p>
  
  <p>The order was closed on or after the first day of February and the order was opened before the first day of February:</p>
  
  <pre><code>SELECT COUNT(Order.No) 'Open', '1' 'Month', '2013' 'Year' FROM Orders
  WHERE (Orders.Open &lt; '2013-02-01') AND (Orders.Close &gt;= '2013-02-01')
  </code></pre>
  
  <p>Now if I want to get this information for each month I would have to do:</p>
  
  <pre><code>SELECT COUNT(Order.No) 'Open', '1' 'Month', '2013' 'Year'  FROM Orders
  WHERE (Orders.Open &lt; '2013-02-01') AND (Orders.Close &gt;= '2013-02-01')
  
  UNION
  
  SELECT COUNT(Order.No) Open, '2' Month, '2013' 'Year'  FROM Orders
  WHERE (Orders.Open &lt; '2013-03-01') AND (Orders.Close &gt;= '2013-03-01')
  
  UNION
  
  SELECT COUNT(Order.No) 'Open', '3' 'Month', '2013' 'Year' FROM Orders
  WHERE (Orders.Open &lt; '2013-04-01') AND (Orders.Close &gt;= '2013-04-01')
  
  UNION
  
  SELECT COUNT(Order.No) 'Open', '4' Month, '2013' Year FROM Orders
  WHERE (Orders.Open &lt; '2013-05-01') AND (Orders.Close &gt;= '2013-05-01')
  </code></pre>
  
  <p>Can I somehow simplify this query so I don't have to write this for every month and year?</p>
  
  <p>The required output would be something like:</p>
  
  <pre>
  Open    Month   Year
  ----    -----   ----
  684     1       2013
  683     2       2013
  760     3       2013
  659     4       2013
  </pre>