'How to concatenate more sql queries to get one table?

I have 4 tables that I have used following queries to get data,

query1 : select * from userdetails1.UserTable;

Result1:                
UserID  Username    Status
1         John      Active
2         Micheal   Active
3         Ann       Active
4         Shasha    Resigned
5         mike      Resigned

query2 : select * from userdetails2.AmountTable;

result2:
UserId  TotalAmount TotalAmountResetDate
1          -12         1/31/2022
2          345         1/31/2022
3          213         1/31/2022
4          0           1/31/2022
5          0           1/31/2022

query3:
select Username,TotalInvested from userdetails2.TotalInvestmentsTable group by Username;

result3:

Username    Totalnvested
John         45000
Micheal      45000
Ann          45000


query4:
select Username,TotalExpenses from userdetails2.TotalExpensesTable group by Username;

Username    TotalExpenses
John           2543
Micheal        2345
Ann            3290

Now I need to get another table by combining all these queries as follows. How should I get that by concatenating all these queries?

UserId  Username    TotalAmountResetDate    TotalAmount Totalnvested    TotalExpenses   TotalBalance
1        John              1/31/2022               -12       45000            2543          42445
2        Micheal           1/31/2022               345       45000            2345          43000
3        Ann               1/31/2022               213       45000            3290          41923
4        Shasha            1/31/2022               0         0                 0            0
5        mike              1/31/2022               0         0                 0            0

Here TotalBalance column need to get from TotalBalance=TotalAmount+Totalnvested -TotalExpenses.Is this possible to get using one query?

Update:

If the total expenses and total investments are more than one value we need to get sum of each user to the total investements. Then how we can get that one?

Example:

Username    Totalnvested
    John         45000
    Micheal      45000
    Ann          45000
    John         21000
    Micheal       5000


Username    TotalExpenses
John           2543
Micheal        2345
Ann            3290
Ann            2345
John            5478
mysqlsql


Solution 1:[1]

You can try to use JOIN and OUTER JOIN to get your expected result.

OUTER JOIN need to base on userdetails1.UserTable because some people might not have Investments or Expenses in those tables, then use COALESCE function to let NULL value be 0

Query #1

SELECT t2.UserID,
       t1.Username,
       t2.TotalAmountResetDate,   
       t2.TotalAmount,   
       COALESCE(Totalnvested,0) Totalnvested,
       COALESCE(TotalExpenses,0) TotalExpenses,
       t2.TotalAmount + COALESCE(Totalnvested,0) - COALESCE(TotalExpenses,0) TotalBalance
FROM userdetails1.UserTable t1
INNER JOIN userdetails2.AmountTable t2
ON t1.UserID = t2.UserID
LEFT JOIN userdetails2.TotalInvestmentsTable investment
ON t1.Username = investment.Username
LEFT JOIN userdetails2.TotalExpensesTable expenses
ON t1.Username = expenses.Username;
UserID TotalAmountResetDate TotalAmount Username Totalnvested TotalExpenses TotalBalance
1 1/31/2022 -12 John 45000 2543 42445
2 1/31/2022 345 Micheal 45000 2345 43000
3 1/31/2022 213 Ann 45000 3290 41923
4 1/31/2022 0 Shasha 0 0 0
5 1/31/2022 0 mike 0 0 0

View on DB Fiddle

Solution 2:[2]

Have you tried Joins command? Inner join, Full join?

https://www.w3schools.com/sql/sql_join.asp

Solution 3:[3]

try like below using join and aggregation

    select ut.UserId,
ut.Username,
ua.TotalAmountResetDate,
sum(TotalAmount) as TotalAmount,
sum(Totalnvested) as TotalInvested,
sum(TotalExpenses) as TotalExpenses,
sum(TotalAmount+coalesce(Totalnvested ,0)- coalesce(TotalExpenses,0)) as TotalBalance

from userdetails1.UserTable ut 
left join
userdetails2.AmountTable ua on ut.UserId=ua.UserId

LEFT JOIN userdetails2.TotalInvestmentsTable env on
ut.Username = env.Username
LEFT JOIN userdetails2.TotalExpensesTable ex
on ut.Username = ex.Username
group by ut.UserId,
ut.Username,
ua.TotalAmountResetDate

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Mehdi Shishehbor
Solution 3

Related Questions