SQL: How to ask for multiple counts from the same table within a join
0
votes
1
answer
5233
views
Trying to find 3 separate counts within one column of a table, each with separate WHERE clauses. I've got 2 tables in the query, so one join. Thought this looked correct but it will not run:
SELECT stores.caption,
COUNT (
WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
AND tasks.deleted_at IS NULL)
AS TaskTotal ,
COUNT (
WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
AND tasks.deleted_at IS NULL
AND tasks.completed_at IS NULL) AS TaskOutstanding,
COUNT (
WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'
AND tasks.deleted_at IS NULL
AND tasks.completed_at IS NULL
AND tasks.deadline < '2019-05-16') AS TaskOverdue
FROM tasks
FULL JOIN stores ON tasks.store_id=stores.id
GROUP BY stores.caption;
Asked by mtug94
(1 rep)
May 16, 2019, 11:03 AM
Last activity: May 16, 2019, 08:05 PM
Last activity: May 16, 2019, 08:05 PM