Question 114853 in PostgreSQL Question posted in
The official documentation can be found here.

postgresql RIGHT Join: limit returned rows

I have the following schema:

expenses

id name, varchar cost, double date, DATE category_id, int f_key user_id, int f_key
1 Pizza 22.9 22/08/2022 1 1
2 Pool 34.9 23/08/2022 2 1

categories

id name, varchar
1 Food
2 Leisure
3 Medicine
4 Fancy food

users_categories(user_id int foreign key, category_id foreign key)

user_id int f_key category_id int f_key
1 1
1 2
1 3
2 4

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:

name count sum
Food 1 22.9
Leisure 1 33.9
Medicine 0 null
Fancy food 0 null

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

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    What I understand is that 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;
    Login or Signup to reply.
  3. 0

    In your query you used user_categories as subquery so it will not filter category ids,

    Try this Query

    SELECT categories.name as name,count(expenses.name) as count, coalesce(round(SUM(price)::numeric,2),0) as sum from 
categories 
left join users_categories on users_categories.category_id= categories.id
left join expenses  ON expenses.category_id = categories.id 
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)
where users_categories.user_id='1' 
GROUP BY categories.name,categories.id ORDER BY categories.id

    OUTPUT :

    name    count   sum
Food    1   22.90
Leisure     1   34.90
Medicine    0   0
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search