title: Trying to select multiple columns on an inner join query with group and where clauses
body: <p>I'm trying to run a query where it will give me one Sum Function, then select two columns from a joined table and then to group that data by the unique id i gave them. This is my original query and it works.</p>
  
  <pre><code>SELECT Sum (Commission_Paid)
  FROM [INTERN_DB2].[dbo].[PaymentList]
  INNER JOIN [INTERN_DB2]..[RealEstateAgentList]
  ON RealEstateAgentList.AgentID = PaymentList.AgentID
  WHERE Close_Date &gt;= '1/1/2013' AND Close_Date &lt;= '12/31/2013'
  GROUP BY RealEstateAgentList.AgentID
  </code></pre>
  
  <p>I've tried the query below, but I keep getting an error and I don't know why. It says its a syntax error.</p>
  
  <pre><code>SELECT Sum (Commission_Paid)
  FROM [INTERN_DB2].[dbo].[PaymentList]
  INNERJOIN [INTERN_DB2]..[RealEstateAgentList](
  Select First_Name, Last_Name
  From [Intern_DB2]..[RealEstateAgentList]
  Group By Last_name
  )
  ON RealEstateAgentList.AgentID = PaymentList.AgentID
  WHERE Close_Date &gt;= '1/1/2013' AND Close_Date &lt;= '12/31/2013'
  GROUP BY RealEstateAgentList.AgentID
  </code></pre>