Join two tables and with sum and fitering

Question

I have two tables Users and Inputs with the following schema

Users - id, name, create_time
Inputs - id, user_id, create_time, amount

I've created 2 similar queries that select join data from the two tables.

The first query returns all users, adding a field called daily_amount which sums all the Inputs of each user in a given time range - Works fine

The second query - Adds a user id filter

I want to limit the query to a specific user by Id (id = 12 in the given query), im getting inconsistent results, I get a single record, but it has a different Id and the daily_amount is incorrect.

Your assistance is appreciated.

-- All users query - Works fine
SELECT Users.id,
       Users.name,
       Users.create_time,
       SUM(Inputs.amount) AS daily_amount
FROM Users
LEFT JOIN Inputs ON Users.id = Inputs.user_id
AND Inputs.create_time BETWEEN startTime AND endTime
GROUP BY Users.id,
         Users.name



-- User specific query
SELECT Users.id,
       Users.name,
       Users.create_time,
       SUM(Inputs.amount) AS daily_amount
FROM Users
LEFT JOIN Inputs ON Users.id = Inputs.user_id
AND Users.id = 12             -- trying to filter only specific user by id
AND Inputs.create_time BETWEEN startTime AND endTime
GROUP BY Users.id,
         Users.name

Answer 1

You must put the condition in the WHERE clause:

SELECT Users.id,
       Users.name,
       Users.create_time,
       SUM(Inputs.amount) AS daily_amount
FROM Users
LEFT JOIN Inputs ON Users.id = Inputs.user_id
WHERE Users.id = 12
AND Inputs.create_time BETWEEN startTime AND endTime
GROUP BY Users.id, Users.name, Users.create_time