CODEWITHSUNDEEP
sqlsql-server
timbstoke
timbstoke

Reputation: 187

SELECT COUNT within date range

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.

Here's what I've got so far: 

SELECT EmailAddress, COUNT(EmailAddress) 
  FROM mails 
  WHERE ReceivedTime >= DATEADD(DAY, -7, GETDATE()) 
  GROUP BY EmailAddress
    HAVING COUNT(EmailAddress) > 10

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: 

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 >= DATEADD(DAY, -7, GETDATE()) 
  GROUP BY EmailAddress
    HAVING COUNT(EmailAddress) > 10

I'm looking for a result set along these lines: 

 EmailAddress     | Deleted | Replied | Read | Total
 ---------------------------------------------------
 [email protected] | 4       | 5       | 3    | 12
 [email protected] | 2       | 6       | 3    | 11

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!

Upvotes: 5

Views: 3051

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

SELECT EmailAddress,
       COUNT(CASE WHEN ActionTaken = 'Deleted' THEN ActionTaken END) AS Deleted,
       COUNT(CASE WHEN ActionTaken = 'Replied' THEN ActionTaken END) AS Replied,
       COUNT(CASE WHEN ActionTaken = 'Read' THEN ActionTaken END)    AS [Read],
       COUNT(EmailAddress) AS Total
FROM mails
WHERE ReceivedTime >= DATEADD(DAY, -7, GETDATE())
GROUP BY EmailAddress
HAVING COUNT(EmailAddress) > 10

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452957

Use single quotes for string literals so it works irrespective of QUOTED_IDENTIFIER settings.

SELECT EmailAddress,
       Sum(CASE WHEN ActionTaken = 'Deleted' THEN 1 ELSE 0 END) AS Deleted,
       Sum(CASE WHEN ActionTaken = 'Replied' THEN 1 ELSE 0 END) AS Replied,
       Sum(CASE WHEN ActionTaken = 'Read' THEN 1 ELSE 0 END)    AS [Read],
       Count(EmailAddress) AS Total
FROM   mails
WHERE  ReceivedTime >= Dateadd(DAY, -7, Getdate())
GROUP  BY EmailAddress
HAVING Count(EmailAddress) > 10

Upvotes: 3

Related Questions