title: SQL Nested Calculation JOIN
body: <p>There are several dozen threads with this title but none of the ones I found seemed to help. </p>
  
  <p>I have a query composed of two tables: Table Customer and Table Transactions</p>
  
  <p>Each Customer can have multiple entries in the Transactions table. What I want to do is for a given time frame (assume dates are tracked within the Transactions table) take each customer in the Customer table and find three different calculations from the Transactions table for that customer. The first number calculates # of quotes issued, the second calculates the # of orders, and the third is the total number of quotes that didn't turn into an order. The calculations themselves really don't matter as it relates to the solution to this problem.</p>
  
  <p>I feel like there is some type of join I'm not utilizing properly to derive the proper values but here is what I have so far (simplified).</p>
  
  <pre><code>SELECT Customer.CustomerID, Count(TransactionAlias1.*), Count(TransactionAlias2.*), Count(TransactionAlias3.*)
  FROM Customer
  LEFT JOIN (SELECT * FROM Transactions WHERE [...]) TransactionAlias1 ON 
       TransactionAlias1.CustomerID = Customer.CustomerID
  LEFT JOIN (SELECT * FROM Transactions WHERE [...]) TransactionAlias2 ON 
       TransactionAlias2.CustomerID = Customer.CustomerID
  LEFT JOIN (SELECT * FROM Transactions WHERE [...]) TransactionAlias3 ON 
      TransactionAlias3.CustomerID = Customer.CustomerID
  GROUP BY Customer.CustomerID
  </code></pre>
  
  <p>What's strange to me is having only the first two inner joins produces the correct values for the first two Counts. Adding the third inner join invalidates the other values. Running the select statement within each inner join alone produces the correct count.</p>
  
  <p>Any help would be greatly appreciated. If any of you are aware of another StackOverflow article that addresses this same issue, please let me know.</p>