PostgreSQL join subquery can’t restrict query

join;postgresqlsubquery

I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances.

I'm trying to run a query that will return an individual users 'amount owed' across all groups (to which they belong).

At the moment the query looks like this:

SELECT 
    SUM(owe) 
FROM (
    SELECT 
        (expenses.amount/count(*))
    AS owe 
    FROM expenses 
    LEFT JOIN user_expenses 
    ON (
        expenses.id = user_expenses.expense_id
        ) 
    WHERE expenses.paid_by != 1 
    GROUP BY expenses.id
    ) 
AS total;

The problem is this query retrieves ALL expenses across the board. What i'd like to do is restrict by using:

WHERE user_expenses.user_id = 1

How can I add that to the aforementioned query (in a subquery) to restrict the result to expenses that only that user is associated with, but still get all users involved in an expense so i can do the math for amount each member owes?

TABLE SCHEMA (if that might help):

USERS
id
name
username
email

EXPENSES
id
created_at
updated_at
title
amount
group_id
paid_by
img_url
note

USER_EXPENSES (join table)
id
expense_id
user_id

GROUPS
id
created_at
name
desc

USER_GROUPS
id
user_id
group_id

Some more sample info for clarification:
if i run this query – 

SELECT 
    e.title,
    e.amount,
    (e.amount/count(*)) AS owe, count(*) AS members 
      FROM expenses e LEFT JOIN
           user_expenses ue
           ON e.id = ue.expense_id
      WHERE e.paid_by != 1 
      GROUP BY e.id;

i get something like this (with dummy data):
enter image description here

If I run the following:

SELECT 
    e.title,
    e.amount,
    (e.amount/count(*)) AS owe, count(*) AS members 
      FROM expenses e LEFT JOIN
           user_expenses ue
           ON e.id = ue.expense_id AND ue.user_id = 1
      WHERE e.paid_by != 1 
      GROUP BY e.id;

I get this (note the members count):
enter image description here

Best Answer

You want to filter out groups of rows rather than individual rows. That is, you want to keep only the groups that have ue.user_id = 1. Therefore use HAVING, rather than WHERE or ON, to add that condition, because HAVING is used for group filtering:

SELECT 
    SUM(owe) 
FROM (
    SELECT 
        (expenses.amount/count(*))
    AS owe 
    FROM expenses 
    LEFT JOIN user_expenses 
    ON (
        expenses.id = user_expenses.expense_id
        ) 
    WHERE expenses.paid_by != 1
    GROUP BY expenses.id
    HAVING COUNT(ue.user_id = 1 OR NULL) > 0
    ) 
AS total;
Related Question