4

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

4

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.