title: SQL query involving a four way join
body: <p>Attempting to create a 4 way query for an MSSQL exercise. Let me describe the tables as briefly I can:</p>
  
  <p>4 main tables, Vendors (vendor details, pk vendorId), Invoices (invoice details pk InvoiceId,fk VendorID), InvoiceLineItems (I) and GlAccounts(accountNo pk).</p>
  
  <p>Query: <em>Write a SELECT statement that returns four columns: VendorName, AccountDescription, LineItemCount, and LineItemSum. LineItemCount is the row count, and LineItemSum is the sum of the InvoiceLineItemAmount column</em></p>
  
  <p>My solution:</p>
  
  <pre><code>SELECT Vendors.VendorName, GLAccounts.AccountDescription, 
    SUM(InvoiceLineItems.InvoiceLineItemAmount) as LineItemSum, 
    Count (InvoiceLineItems.InvoiceID) as LineItemCount
  FROM Vendors  INNER JOIN Invoices
  ON Vendors.VendorID = Invoices.VendorID 
  INNER JOIN InvoiceLineItems
    ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
  INNER JOIN GLAccounts
    ON InvoiceLineItems.AccountNo = GLAccounts.AccountNo
  ORDER BY  Vendors.VendorName
  </code></pre>
  
  <p>THE ISSUE:</p>
  
  <p>Msg 8120, Level 16, State 1, Line 1
  Column 'Vendors.VendorName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  
  <p>THE DILEMMA:</p>
  
  <p>Not sure why this problem is happening because very clearly I mention Vendors.VendorName  in the Group By clause. I suspect it has something to do with the inner joins I am performing since they are multiple, I must have got something wrong. </p>
  
  <p>Can anyone spot what's wrong here?</p>