snippetMinor
How to SELECT multiple COUNT(*) columns with each GROUP BY having a different WHERE clause?
Viewed 0 times
eachcolumnsgroupwithwhereselecthavingdifferentmultiplehow
Problem
I have the following schema:
and the following data:
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:
However, the query I am trying is returning the following:
but I would like
(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:
`
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.PersonIdHowever, the query I am trying is returning the following:
PersonId | GroupA_Actions_Made | GroupB_Actions_Made
(null) 0 1but 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.PersonIdCode 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.PersonIdContext
StackExchange Database Administrators Q#65416, answer score: 6
Revisions (0)
No revisions yet.