SQL - How do I join multiple tables and add appropriately with SUM?-CodePudding

I'm trying to join multiple tables (Microsoft SQL Server Management Studio v.18.9.1) together (based on an ID) and have it SUM out a new column using a sum aggregate. Here are my tables:

Table: California

CaliforniaID	Name	AmountCalifornia
C1	Alan	1.00
C1	Alan	1.00
C1	Alan	1.00
C1	Alan	2.00
C2	Eric	2.00
C2	Eric	2.00
C2	Eric	3.00
C3	Janero	3.00
C3	Janero	3.00
C3	Janero	5.00

Table: Texas

TexasID	Name	AmountTexas
T2	Eric	2.01
T2	Eric	2.01
T2	Eric	2.01
T3	Jan	3.01
T3	Jan	3.01
T4	Lil	4.01

Table: Florida

FloridaID	Name	AmountFlorida
F5	Manny	10.00
F5	Manny	10.00
F6	Nina	11.00
F3	Jan	100.00
F4	Lily	27.00

Table Junction1 that I created to be able to Join any tables that I need to in the future

All of the respective IDs match, so that I have something to join by

CaliforniaID	TexasID	FloridaID	Name
C1	T1	F1	Alan B.
C2	T2	F2	Eric D.
C3	T3	F3	Janero T.
C4	T4	F4	Lila E.
C5	T5	F5	Manuello R.
C6	T6	F6	Nina H.
C7	T7	F7	Perry R.
C8	T8	F8	Ramos T.
C9	T9	F9	Skye F.
C10	T10	F10	Trinity A.

When I run the following query:

SELECT TOP 10 
    j.name, 
    COALESCE(SUM(t.amount1), 0) AS CaliforniaExpenses, 
    COALESCE(SUM(t.amount2), 0) AS TexasExpenses,    
    COALESCE(SUM(t.amount1), 0)   COALESCE(SUM(t.amount2), 0) AS TotalExpenses
FROM
    junction1 j
LEFT JOIN
    (SELECT 
         CaliforniaID, null AS TexasID, 
         AmountCalifornia AS amount1, null AS amount2 
     FROM
         test1 
     UNION ALL
     SELECT
         null, TexasID, null, AmountTexas 
     FROM
         test2) t ON t.CaliforniaID = j.CaliforniaID 
                     OR t.TexasID = j.TexasID 
GROUP BY
    j.name 
ORDER BY 
    CaliforniaExpenses DESC;

Result

Name	CaliforniaExpenses	TexasExpenses	TotalExpenses
Janero T.	11.00	6.02	17.02
Eric D.	7.00	6.03	13.03
Alan B.	5.00	0.00	5.00
Trinity A.	0.00	0.00	0.00
Skye F.	0.00	0.00	0.00
Ramos T.	0.00	0.00	0.00
Perry R.	0.00	0.00	0.00
Nina H.	0.00	0.00	0.00
Manuello R.	0.00	0.00	0.00
Lila E.	0.00	33.06	33.06

Which is great so far! Is there any possible way to sneak in the Florida column FloridaExpenses (right after the TexasExpenses Alias) and have the total reflected for it as well?

Any help is greatly appreciated!

CodePudding user response：

Step 1. Aggregate each State's Expenses table to ID level.

Step 2. Left outer join from Junction1 to the other three on ID column

with 
cte_ca as (select CaliforniaID, sum(AmountCalifornia) as AmountCalifornia from California group by CaliforniaID),
cte_tx as (select TexasID, sum(AmountTexas) as AmountTexas from Texas group by TexasID),
cte_fl as (select FloridaID, sum(AmountFlorida) as AmountFlorida from Florida group by FloridaID)
select j.Name,
       coalesce(sum(ca.AmountCalifornia),0) as CaliforniaExpenses,
       coalesce(sum(tx.AmountTexas),0) as TexasExpenses,
       coalesce(sum(fl.AmountFlorida),0) as FloridaExpenses,
       coalesce(sum(ca.AmountCalifornia),0)    coalesce(sum(tx.AmountTexas),0)   coalesce(sum(fl.AmountFlorida),0) as TotalExpenses
  from Junction1 j
  left
  join cte_ca ca on j.CaliforniaID = ca.CaliforniaID
  left
  join cte_tx tx on j.TexasID = tx.TexasID
  left
  join cte_fl fl on j.FloridaID = fl.FloridaID
 group by j.name
 order by j.name;

Outcome:

Name       |CaliforniaExpenses|TexasExpenses|FloridaExpenses|TotalExpenses|
----------- ------------------ ------------- --------------- ------------- 
Alan B.    |            5.0000|       0.0000|         0.0000|       5.0000|
Eric D.    |            7.0000|       6.0300|         0.0000|      13.0300|
Janero T.  |           11.0000|       6.0200|       100.0000|     117.0200|
Lila E.    |            0.0000|       4.0100|        27.0000|      31.0100|
Manuello R.|            0.0000|       0.0000|        20.0000|      20.0000|
Nina H.    |            0.0000|       0.0000|        11.0000|      11.0000|
Perry R.   |            0.0000|       0.0000|         0.0000|       0.0000|
Ramos T.   |            0.0000|       0.0000|         0.0000|       0.0000|
Skye F.    |            0.0000|       0.0000|         0.0000|       0.0000|
Trinity A. |            0.0000|       0.0000|         0.0000|       0.0000|

db<>fiddle