SQL Server – How to SELECT Multiple COUNT(*) with Different WHERE Clauses

group byjoin;sql-server-2008-r2

I have the following schema:

CREATE TABLE Person (
  PersonId int PRIMARY KEY
)

CREATE TABLE Action (
  ActionId int PRIMARY KEY,
  PersonId int NOT NULL FOREIGN KEY REFERENCES Person(PersonId),
  ActionTime datetime NOT NULL
)

and the following data:

INSERT INTO Person (PersonId) VALUES
(1),
(2),
(3),
(4)

INSERT INTO Action (ActionId, PersonId, ActionTime) VALUES
(1, 1, '2014-02-01'),
(2, 1, '2014-02-02'),
(3, 2, '2014-02-02'),
(4, 3, '2014-03-05')

I would like to run a query which shows the number of actions each person performs between the 15th of each month. For example, I am trying the following:

SELECT
    Person.PersonId,
    COALESCE(GroupA_Actions_Made, 0) AS GroupA_Actions_Made,
    COALESCE(GroupB_Actions_Made, 0) AS GroupB_Actions_Made
FROM
    Person
    RIGHT OUTER JOIN (
        SELECT
            PersonId,
            COUNT(*) AS GroupA_Actions_Made
        FROM
            Action
        WHERE
            ActionTime BETWEEN '2014-01-15 00:00:00' AND '2014-02-14 23:59:59'
        GROUP BY
            PersonId
    ) GroupA ON GroupA.PersonId = Person.PersonId
    RIGHT OUTER JOIN (
        SELECT
            PersonId,
            COUNT(*) AS GroupB_Actions_Made
        FROM
            Action
        WHERE
            ActionTime BETWEEN '2014-02-15 00:00:00' AND '2014-03-14 23:59:59'
        GROUP BY
            PersonId
    ) GroupB ON GroupB.PersonId = Person.PersonId

However, the query I am trying is returning the following:

PersonId | GroupA_Actions_Made | GroupB_Actions_Made
(null)     0                     1

but I would like

PersonId | GroupA_Actions_Made | GroupB_Actions_Made
1          2                     0
2          1                     0
3          0                     1

(I do not want the results to return anything for people who have not made actions.)

How can I obtain the results in the desired format?

UPDATE

Each of the answers works except that I had to wrap them as follows:

SELECT
    PersonId,
    GroupA_Actions_Made,
    GroupB_Actions_Made
FROM (
    -- (answer)
) t
WHERE
    GroupA_Actions_Made > 0
    OR GroupB_Actions_Made > 0

Using SQL Server Profiler, the accepted answer seems to have the fastest query time on large data sets.

Best Answer

This will give you the result you wanted but I'm not sure if it's the most flexible piece of code.

SELECT p.PersonId,
    SUM(CASE 
          WHEN a.ActionTime >= '2014-01-15 00:00:00' 
            AND a.ActionTime < '2014-02-15 00:00:00'
          THEN 1 
          ELSE 0 
        END) AS GroupA_Actions_Made,
    SUM(CASE 
          WHEN a.ActionTime >= '2014-02-15 00:00:00' 
            AND a.ActionTime < '2014-03-15 00:00:00'
          THEN 1 
          ELSE 0 
        END) AS GroupB_Actions_Made
FROM
    Person p
JOIN
    Action a on p.PersonId = a.PersonId
GROUP BY p.PersonId
Related Question