title: SELECT COUNT within date range
body: <p>I'm trying to get a count of emails received during a date range using MSSQL Express 2005. I've managed that much, but now I want to split out the data into what was done with it. </p>
  
  <p>Here's what I've got so far: </p>
  
  <pre><code>SELECT EmailAddress, COUNT(EmailAddress) 
    FROM mails 
    WHERE ReceivedTime &gt;= DATEADD(DAY, -7, GETDATE()) 
    GROUP BY EmailAddress
      HAVING COUNT(EmailAddress) &gt; 10
  </code></pre>
  
  <p>That gives me a list of addresses who mailed more than 10 times in the last week, but we have another column that tells us what happened with the mail. I'm trying to achieve something like this: </p>
  
  <pre><code>SELECT EmailAddress, 
    COUNT(ActionTaken WHERE ActionTaken="Deleted") AS Deleted,
    COUNT(ActionTaken WHERE ActionTaken="Replied") AS Replied,
    COUNT(ActionTaken WHERE ActionTaken="Read") AS Read,
    COUNT(EmailAddress) AS Total
    FROM mails 
    WHERE ReceivedTime &gt;= DATEADD(DAY, -7, GETDATE()) 
    GROUP BY EmailAddress
      HAVING COUNT(EmailAddress) &gt; 10
  </code></pre>
  
  <p>I'm looking for a result set along these lines: </p>
  
  <pre><code> EmailAddress     | Deleted | Replied | Read | Total
   ---------------------------------------------------
   user1@domain.com | 4       | 5       | 3    | 12
   user2@domain.com | 2       | 6       | 3    | 11
  </code></pre>
  
  <p>I'm pretty sure it has something to do with using OVER(PARTITION BY) clauses, but I've hit a wall. I realise the ActionTaken field isn't ideal, but I didn't build it!</p>