I have two queries counting and grouping on different columns:
Get the count of items owned to each user:
SELECT
user_id,
COUNT(id)
FROM items
WHERE user_id IN (?)
GROUP BY user_id
Get the count of items created by each user:
SELECT
created_by_user_id,
COUNT(id)
FROM items
WHERE created_by_user_id IN (?)
GROUP BY created_by_user_id
There’s nothing too complex going on in these, and I’m trying to see if there are ways to get the same results with a single query.
From what I’ve read, it might be possible to get it to work using FILTER
(it is incomplete as I’m still trying to figure it out):
SELECT
user_id,
COUNT(id) FILTER (WHERE user_id = ???) AS "Owned",
COUNT(id) FILTER (WHERE created_by_agent_id = ???) AS "Created"
FROM items
But since my queries GROUP BY
on a different row, I’m unsure this approch would fit my use case.
Am I on the right track with FILTER
or is there something I’ve missed?