I have the following schema:

expenses

categories

users_categories(user_id int foreign key, category_id foreign key)

And two users with id 1 and 2.

Relation between user and category is many to many.

Problem:

I want to get statistics (total cost amount and count) for all categories. For categories where there are no expenses I want to return 0. Here is my query:

SELECT categories.name as name, count(expenses.name) as count, round(SUM(price)::numeric,2) as sum
FROM expenses
Right JOIN categories ON expenses.category_id = categories.id
and expenses.category_id in (
 select users_categories.category_id from users_categories where users_categories.user_id = 1
)
and expenses.id in(
 Select expenses.id from expenses
  join users_categories on expenses.category_id = users_categories.category_id
  and expenses.user_id = 1
  AND (extract(year from date) = 2022 OR CAST(2022 AS int) is null)
  AND (extract(month from date) = 8 OR CAST(8 AS int) is null)
)
GROUP BY categories.id ORDER BY categories.id

The response is:

How I should edit my query to eliminate the last row, because this category doesn't belong to the user 1.

CodePudding user response：

You want to move expenses.category_id in ... out of the ON condition and into a WHERE clause.

When it is in the ON clause, that means rows which were removed by the in-test just get NULL-fabricated anyway. You want to remove those rows after the NULL-fabrication is done, so that they remain removed. But why do you use that in-test anyway? Seems like it would be much simpler written as another join.

CodePudding user response：

What I understood is you are trying to get the count and sum of expenses for all the categories related to the user_id 1 within the month of august 2022.

Please try out the following query.

WITH statistics AS (SELECT e.category_id, count(e.*) as count, round(sum(e.cost),2) as sum FROM expenses e WHERE e.user_id=1 AND (e.date BETWEEN '01/08/2022' AND '31/08/2022') GROUP BY e.category_id), user_category as(SELECT uc.category_id, COALESCE(s.count,0) as count, COALESCE(s.sum,0) as sum FROM users_categories uc LEFT JOIN statistics s ON uc.category_id=s.id WHERE uc.user_id=1) SELECT c.name, u.count, u.sum FROM categories c INNER JOIN user_category u ON u.category_id=c.id;