title: Grouping Multiple Counts on Pivot Table by Year
body: <p>I have just started learning SQL. I've written the following:</p>
  
  <pre><code>DECLARE @DateFrom Date = '01-Jan-2014', @DateTo Date = '31-Dec-2014'
  
  SELECT TotalCalls, UniqueCalls, TotalEmails, UniqueEmails, AgentsContacted, Instructed FROM 
  
  (   SELECT *, 
  
  (SELECT YEAR(EventDate)) AS year,
  
  (SELECT COUNT(*) FROM Events JOIN dbo.ContactType 
                      ON EventContactType=ContactTypeID 
                      WHERE ContactTypeName = 'Call' AND EventDate &gt;= @DateFrom AND EventDate &lt;= @DateTo) AS TotalCalls,
  
  (SELECT COUNT(Distinct EventAgentID) FROM Events JOIN dbo.ContactType 
                      ON EventContactType=ContactTypeID
                       WHERE ContactTypeName = 'Call' AND EventDate &gt;= @DateFrom AND EventDate &lt;= @DateTo) AS UniqueCalls,
  
  (SELECT COUNT(*) FROM Events JOIN dbo.ContactType 
                      ON EventContactType=ContactTypeID 
                      WHERE ContactTypeName = 'Email' AND EventDate &gt;= @DateFrom AND EventDate &lt;= @DateTo) AS TotalEmails,
  
  (SELECT COUNT(Distinct EventAgentID) FROM Events JOIN dbo.ContactType 
                      ON EventContactType=ContactTypeID 
                      WHERE ContactTypeName = 'Email' AND EventDate &gt;= @DateFrom AND EventDate &lt;= @DateTo) AS UniqueEmails,
  
  (SELECT COUNT(DISTINCT EventAgentID)
  FROM Events 
  JOIN dbo.ContactType 
  ON EventContactType=ContactTypeID
  LEFT JOIN (SELECT AgentID, (SELECT CASE WHEN AgentDateOfRecentInstruction Is Null OR 
                      AgentDateOfRecentInstruction &lt; DATEADD(month, -12, @DateFrom) --for sp change get date to @FromDate
                      THEN 'NO' ELSE 'YES' END) AS InstructedWithinPastYear FROM Agents) ti
  ON Events.EventAgentID=ti.AgentID
  WHERE EventToFrom='1' 
  AND (ContactTypeName = 'Email' OR ContactTypeName = 'Call')
  AND InstructedWithinPastYear = 'NO'
  AND (EventDate &gt;= @DateFrom AND EventDate &lt;= @DateTo)) AS AgentsContacted,
  
  (SELECT COUNT(DISTINCT EventAgentID)
      FROM Events 
      Join dbo.AGents
      ON EventAgentID=AgentID
      JOIN dbo.ContactType 
      ON EventContactType=ContactTypeID
      LEFT JOIN (SELECT AgentID, (SELECT CASE WHEN AgentDateOfRecentInstruction Is Null OR 
                          AgentDateOfFirstIntsruction &lt; @DateFrom --for sp change get date to @FromDate
                          THEN 'NO' ELSE 'YES' END) AS InstructedWithinPastYear FROM Agents) ti
      ON Events.EventAgentID=ti.AgentID
      WHERE EventToFrom='1' 
      AND (ContactTypeName = 'Email' OR ContactTypeName = 'Call')
      AND (AgentDateOfRecentInstruction &lt;= @DateTo AND AgentDateOfRecentInstruction &gt;= @DateFrom)
      AND (EventDate &gt;= @DateFrom AND EventDate &lt;= @DateTo)) AS Instructed    
  
      FROM Events
      JOIN dbo.ContactType 
      ON EventContactType=ContactTypeID
      Join dbo.AGents
      ON EventAgentID=AgentID
  ) 
  as s
  
  PIVOT (count(EventAgentID) FOR year
  IN ([2013],[2014]) ) pvt
  </code></pre>
  
  <p>Which returns thousands of row of this:</p>
  
  <pre>
  TotalCalls | UniqueCalls | TotalEmails | UniqueEmails | AgentsContacted | Instructed
  169        | 106     | 202         | 125          | 24              | 15
  169        | 106     | 202         | 125          | 24              | 15
  169        | 106     | 202         | 125          | 24              | 15
  </pre>
  
  <p>I want to it to return:</p>
  
  <pre>
  Year | TotalCalls | UniqueCalls | TotalEmails | UniqueEmails | AgentsContacted
  2014 | 169        | 106         | 202         | 125      | 24
  2013 | 69         | 68          | 112         | 86       | 13
  </pre>
  
  <p>I'm fairly certain I'm doing a few things wrong here but I wasn't able to write my question succinctly enough to work out how to do it from searches.</p>
  
  <p>(note: The local variables are there temporarily whilst I write the procedure)</p>