title: Joining three tables with sum in SQL Server
body: <p>I have three tables:   </p>
  
  <ol>
  <li><p><code>Sales.SalesOrderHeader</code>:</p>
  
  <ul>
  <li><code>SalesOrderID</code> (PK)</li>
  <li><code>OrderDate</code></li>
  <li><code>TotalDue,SalesPersonID</code></li>
  <li>...</li>
  </ul></li>
  <li><p><code>Sales.SalesPerson</code>:</p>
  
  <ul>
  <li><code>BusinessEntityID</code> (PK)</li>
  <li>...</li>
  </ul></li>
  <li><p><code>Person.Person</code>:</p>
  
  <ul>
  <li><code>BusinessEntityID</code> (PK)</li>
  <li><code>FirstName</code></li>
  <li><code>LastName</code></li>
  <li>...</li>
  </ul></li>
  </ol>
  
  <p>The table <code>SalesPerson</code> acts like an associate entity, which only contributes a common PK (same name as in <code>Person</code> and named as <code>SalesPersonID</code> in <code>SalesOrderHeader</code>).</p>
  
  <p>Now I want to display a table like the following:</p>
  
  <pre><code>   BusinessEntityID  FirstName  LastName  Total attributed sales
     ----------------  ---------  --------  ----------------------
  1  285               Saeeed     Abbas     32323.88
  2  287               Amy        Albert    1214323.98
  …  …                 …          …         …
  </code></pre>
  
  <p>There are some <code>salesOrders</code> with <code>null</code> sales Person, so I want to ignore that, and sum up the sales (sum up the <code>TotalDue</code>) for the year 2008 by each sales person. </p>
  
  <p>This is my T-SQL code so far:</p>
  
  <pre><code>select
      SUM(s.TotalDue) as 'Total attributed sales',
      p.BusinessEntityID,
      p.FirstName,
      p.LastName
  from Sales.SalesOrderHeader as s
  right outer join Sales.SalesPerson as sp on s.SalesPersonID=sp.BusinessEntityID 
  left outer join Person.Person as p on sp.BusinessEntityID=p.BusinessEntityID
  where 
  s.OrderDate between '2008-01-01 00:00:00.000' and '2008-12-31 23:59:59.999'
  and SalesPersonID is not null
  group
  --Order 
  by SalesOrderID, 
  SalesPersonID
  </code></pre>
  
  <p>There are no syntax errors, but my query does not work as intended.</p>
  
  <p>The SQL Server's error message is like</p>
  
  <blockquote>
    <p>Column 'Person.Person.BusinessEntityID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  </blockquote>
  
  <p>So could anyone help me on this please? Your help is much appreciated. </p>