title: Multiple Calculations in SQL SELECT Query from Multiple Tables using Where?
body: <p>I have 2 Tables,  Inventory And Transactions,  Inventory Contains details about the Inventory Item.  Transactions is linked via PK to record goods in / goods out quantities.</p>
  
  <p>I am attempting to get a table that not only displays the Inventory Details,  But also the Transaction Totals for each Inventory Row.</p>
  
  <p>Currently What I am working with is:</p>
  
  <pre><code>SELECT Inventory.InvID,
  sum(case when ITT.TransactionTypeName = 'GoodsIn' then Quantity else 0 end) As Goods_In
  sum(case when ITT.TransactionTypeName = 'GoodsOut' then Quantity else 0 end) As Goods_Out
  FROM
  Inventory
  INNER JOIN Transactions As IT ON Inventory.InvID = IT.InvID
  INNER JOIN TransactionType AS ITT ON IT.TransactionType = ITT.TransactionTypeID
  GROUP BY ITT.TransactionTypeName
  </code></pre>
  
  <p>I found this on Stack Overflow from another post and through various tests and issues cannot seem to get it to provide the correct Data.</p>
  
  <p>With this I have a few questions:</p>
  
  <ul>
  <li>Is it possible to perform calculations in a query such as Above?</li>
  <li>Can someone Point me in the right direction to solve this particular Query?</li>
  <li>Am I using the Correct Join for this task? and/or is a Union Required?</li>
  </ul>
  
  <p><strong>Edit</strong></p>
  
  <p>Through further Testing I did get this working and It was my own Silly mistake.  I had to Group it By a Common Field (in this case Inventory.InvID)</p>
  
  <p><strong>Edit2</strong></p>
  
  <p>This does not however appear to return values that have no Transactions (e.g Empty or Unchecked items)</p>