title: How to create a summary view of Master/Detail Tables using SQL
body: <p>I have two (simplified) tables/classes set up like this:</p>
  
  <pre><code>public class OrderHeader
  {
      public OrderHeader()
      {
          Details = new HashSet&lt;OrderDetail&gt;();
      }
  
      [Key]
      public int TransactionId { get; set; }
      public string CustomerName { get; set; }
      public virtual ICollection&lt;OrderDetail&gt; Details { get; set; }
  }
  
  public class OrderDetail
  {
      [Key]
      public int DetailId { get; set; }
      public int ItemId { get; set; }
      public int QuantityOrdered { get; set; }
      public int QuantityShipped { get; set; }
      public int QuantityRemaining { get; set; }
      public int TransactionId { get; set; }
  
      [ForeignKey("TransactionId")]
      public virtual OrderHeader Header { get; set; }
  }
  </code></pre>
  
  <p>I am trying to create a view to return:</p>
  
  <ol>
  <li>The <code>TransactionId</code></li>
  <li>A count of all details by <code>OrderHeader</code> as column <code>Number of Details</code></li>
  <li>A count of details by <code>OrderHeader</code> where <code>QuantityRemaining</code> equals zero as columnn <code>Completed</code></li>
  <li>A count of details by <code>OrderHeader</code> named <code>Partially Shipped</code> where <code>QuantityShipped</code> is greater than zero but less than <code>QuantityOrdered</code></li>
  </ol>
  
  <p>I have tried querying both the <code>OrderHeader</code> and the <code>OrderDetails</code> tables and trying to make sub queries for each column I wanted but I wind up getting multiple lines with the same <code>TransactionId</code>. And when I use the <code>DISTINCT</code> select the numbers are off from what I know to be correct.</p>
  
  <p>While admitting this is utterly pitiful, I can get a partial but accurate result set with only two columns:</p>
  
  <pre><code>    SELECT
          TransactionId,
          COUNT(*) AS [Number of Details]
      FROM OrderDetails
      GROUP BY TransactionID
  </code></pre>
  
  <p>But I can not get it working with the other two columns.</p>