I have 3 tables, users
, user_spents
, and user_incomes
.
user_spents
anduser_incomes
tables contains activities of users per date.- There can be multiple activities in one date per user. (e.g.
user
can have multiple spent or income in 2022-04-01 date). - It's not mandatory that user must have
spent
orincome
in a given date
I want to select activities of single user grouped and filtered by date.
Sql Fiddle Link and sql What I have tried so far: http://sqlfiddle.com/#!9/846c851/1
What am I doing wrong? Can you give me any suggestions?
Example data what I want to achieve:
name spent income date
----------------------------------
Jack 8 12 2022-04-01
Jack 4 56 2022-04-02
Jack NULL 44 2022-04-03
Jack 7 NULL 2022-04-04
Sample data. (Alse included in sql fiddle)
Users Table:
id name
1 Jack
User Spents:
id user_id spent date
1 1 2 2022-04-01
2 1 1 2022-04-01
3 1 5 2022-04-01
4 1 3 2022-04-02
5 1 1 2022-04-02
User Spents:
id user_id income date
1 1 44 2022-04-03
2 1 15 2022-04-02
3 1 41 2022-04-02
4 1 12 2022-04-01
5 2 54 2022-04-01
Any help will be appreciated. Thanks.
CodePudding user response:
You can use UNION ALL
to get all the rows of user_spents
and user_incomes
and then join to users
to aggregate:
SELECT u.id, u.name,
SUM(t.spent) spent,
SUM(t.income) income,
t.date
FROM users u
INNER JOIN (
SELECT user_id, spent, null income, date FROM user_spents
UNION ALL
SELECT user_id, null, income, date FROM user_incomes
) t ON t.user_id = u.id
WHERE u.name = 'Jack' -- remove this condition to get results for all users
GROUP BY u.id, t.date;
See the demo.