HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How to SELECT multiple COUNT(*) columns with each GROUP BY having a different WHERE clause?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
eachcolumnsgroupwithwhereselecthavingdifferentmultiplehow

Problem

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:

`

Solution

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' 
            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

Code Snippets

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

Context

StackExchange Database Administrators Q#65416, answer score: 6

Revisions (0)

No revisions yet.