How to concatenate more sql queries to get one table?-CodePudding

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?

CodePudding user response：

Use subquery for retrieving user name wise total invested and total expense and then use INNER JOIN between user table and amount table and also use LEFT JOIN for invested and expense related result with this for getting desired result. Use aggregate function like SUM() in inside subquery because using group by in given query. If only single row is existed in both invested and expense table then no need to use group by. At this case aggregate function will not use.

select t1.UserID
     , t1.Username
     , t2.TotalAmountResetDate
     , t2.TotalAmount
     , t3.TotalInvested
     , t4.TotalExpenses
     , t2.TotalAmount   COALESCE(t3.TotalInvested, 0) - COALESCE(t4.TotalExpenses, 0) TotalBalance 
from userdetails1.UserTable t1
inner join userdetails2.AmountTable t2
        on t1.UserID = t2.UserID
left join (select Username, SUM(TotalInvested) TotalInvested
           from userdetails2.TotalInvestmentsTable 
           group by Username) t3
       on t1.Username = t3.Username
left join (select Username, SUM(TotalExpenses) TotalExpenses 
           from userdetails2.TotalExpensesTable 
           group by Username) t4
       on t1.Username = t4.Username

CodePudding user response：

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

CodePudding user response：

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

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

CodePudding user response：

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