Union All and average matching columns

Question

Hi All,

I really need some help with this scenario. I have a table with historical transactions for previous years (2018, 2019, 2020) and I want to be able to combine those historical records and produce a "suggested set" for 2021 in this example.

There might be different line items in each year.. so I need a Union All to get all line items, not just duplicates... but whilst doing the union all I want to create a single line item for rows that match. So if theres a matching row in 2019 and 2020 then it averages them for 2021, rather than giving me multiple rows.

I am currently loading 2018, 2019 and 2020 out into table variables and now want to create me new "suggested for 2020" table set.

I hope that scenario makes sense. If not I can try to build some sample tables to demonstrate.

TIA

EDIT: This is as far as I have gotten:

I identify "matching" rows by the combination of their [wbs] and [cost_element_id] fields. So, in the example below. I should end up with 11 rows, with wbs-1, 2 being averaged into a single row. I hope that makes sense.

I believe my code below gets the the right amount of rows. But the average calculation is wrong. I need to set how many to divide the sum by by how many rows of that combination exist. So, most rows are divide by 1, but wbs-1, 2 should be divide by 3. And, table 4 is intentionally empty because my import could contain UP TO 4 tables. (actually 5, but I stopped at 4 in this sample).

DECLARE @model_name nvarchar(50) = ''  
DECLARE @seed_actuals nvarchar(50) = '2018,2019,2010'  
DECLARE @created_by nvarchar(50) = ''  
  
DECLARE @virtual_table1 TABLE(  
 [fin_transactions_id] [int] IDENTITY(1,1) NOT NULL,  
    [subfunction_id] [int] NULL,  
 [wbs] [varchar](50) NULL,  
 [cost_element_id] [int] NULL,  
 [amount] [decimal](19, 4) NULL  
)  
  
DECLARE @virtual_table2 TABLE(  
 [fin_transactions_id] [int] IDENTITY(1,1) NOT NULL,  
    [subfunction_id] [int] NULL,  
 [wbs] [varchar](50) NULL,  
 [cost_element_id] [int] NULL,  
 [amount] [decimal](19, 4) NULL  
)  
  
DECLARE @virtual_table3 TABLE(  
 [fin_transactions_id] [int] IDENTITY(1,1) NOT NULL,  
    [subfunction_id] [int] NULL,  
 [wbs] [varchar](50) NULL,  
 [cost_element_id] [int] NULL,  
 [amount] [decimal](19, 4) NULL  
)  
  
DECLARE @virtual_table4 TABLE(  
 [fin_transactions_id] [int] IDENTITY(1,1) NOT NULL,  
    [subfunction_id] [int] NULL,  
 [wbs] [varchar](50) NULL,  
 [cost_element_id] [int] NULL,  
 [amount] [decimal](19, 4) NULL  
)  
  
DECLARE @virtual_table_averages TABLE(  
 [fin_transactions_id] [int] IDENTITY(1,1) NOT NULL,  
    [subfunction_id] [int] NULL,  
 [wbs] [varchar](50) NULL,  
 [cost_element_id] [int] NULL,  
 [amount] [decimal](19, 4) NULL  
)  
  
INSERT INTO @virtual_table1  
(  
    [subfunction_id],  
    [wbs],  
    [cost_element_id],  
    [amount]  
)  
VALUES  
(  
    1,  
    'wbs-1',  
    1,  
    5  
),  
(  
    1,  
    'wbs-1',  
    2,  
    10  
),  
(  
    1,  
    'wbs-1',  
    3,  
    10  
)  
  
INSERT INTO @virtual_table2  
(  
    [subfunction_id],  
    [wbs],  
    [cost_element_id],  
    [amount]  
)  
VALUES  
(  
    1,  
    'wbs-2',  
    1,  
    5  
),  
(  
    1,  
    'wbs-1',  
    2,  
    20  
),  
(  
    1,  
    'wbs-2',  
    3,  
    10  
),  
(  
    1,  
    'wbs-2',  
    4,  
    10  
),  
(  
    1,  
    'wbs-2',  
    5,  
    10  
)  
  
INSERT INTO @virtual_table3  
(  
    [subfunction_id],  
    [wbs],  
    [cost_element_id],  
    [amount]  
)  
VALUES  
(  
    1,  
    'wbs-3',  
    1,  
    5  
),  
(  
    1,  
    'wbs-1',  
    2,  
    20  
),  
(  
    1,  
    'wbs-2',  
    3,  
    10  
),  
(  
    1,  
    'wbs-3',  
    4,  
    10  
),  
(  
    1,  
    'wbs-3',  
    5,  
    10  
)  
  
-- DECLARE @table_count int  
  
-- IF EXISTS (SELECT [fin_transactions_id] FROM @virtual_table1)  
--     IF EXISTS (SELECT [fin_transactions_id] FROM @virtual_table2)  
--        IF EXISTS (SELECT [fin_transactions_id] FROM @virtual_table3)  
--            IF EXISTS (SELECT [fin_transactions_id] FROM @virtual_table4)  
--                SET @table_count = 4  
--            ELSE  
--                SET @table_count = 3  
--        ELSE  
--            SET @table_count = 2  
--    ELSE  
--        SET @table_count = 1  
-- ELSE  
--    SET @table_count = 0  
  
 INSERT INTO @virtual_table_averages  
     SELECT  
         COALESCE(A.[subfunction_id], B.[subfunction_id], C.[subfunction_id], D.[subfunction_id]),  
         COALESCE(A.[wbs], B.[wbs], C.[wbs], D.[wbs]),  
         COALESCE(A.[cost_element_id], B.[cost_element_id], C.[cost_element_id], D.[cost_element_id]),  
         (COALESCE(A.[amount],'0') + COALESCE(B.[amount],'0') +  COALESCE(C.[amount],'0')  + COALESCE(D.[amount],'0')) / (  
                         SELECT  
                                 COUNT(*)  
                             FROM @virtual_table1 AS W  
                             JOIN @virtual_table2 AS X ON W.[wbs] = X.wbs AND W.cost_element_id = X.cost_element_id   
                             JOIN @virtual_table3 AS Y ON W.[wbs] = Y.wbs AND W.cost_element_id = Y.cost_element_id  
                             JOIN @virtual_table4 AS Z ON W.[wbs] = Z.wbs AND W.cost_element_id = Z.cost_element_id  
                             WHERE   
                             W.[wbs] = A.[wbs] AND W.[cost_element_id] = A.[cost_element_id])  
                  AS amount  
     FROM @virtual_table1 AS A   
     FULL OUTER JOIN @virtual_table2 AS B ON A.[wbs] = B.wbs AND A.cost_element_id = B.cost_element_id   
     FULL OUTER JOIN @virtual_table3 AS C ON A.[wbs] = C.wbs AND A.cost_element_id = C.cost_element_id  
     FULL OUTER JOIN @virtual_table4 AS D ON A.[wbs] = D.wbs AND A.cost_element_id = D.cost_element_id  
  
  
-- SELECT count(*) AS virtual_table1 FROM @virtual_table1  
  
-- SELECT count(*) AS virtual_table2 FROM @virtual_table2  
  
-- SELECT count(*) AS virtual_table_averages FROM @virtual_table_averages  
  
Select * from @virtual_table_averages  
  
  
GO

What I need to do is get the average working. I know this is totally wrong, but its as far as I have got tonight!

(COALESCE(A.[amount],'0') + COALESCE(B.[amount],'0') +  COALESCE(C.[amount],'0')  + COALESCE(D.[amount],'0')) / (  
                         SELECT  
                                 COUNT(*)  
                             FROM @virtual_table1 AS W  
                             JOIN @virtual_table2 AS X ON W.[wbs] = X.wbs AND W.cost_element_id = X.cost_element_id   
                             JOIN @virtual_table3 AS Y ON W.[wbs] = Y.wbs AND W.cost_element_id = Y.cost_element_id  
                             JOIN @virtual_table4 AS Z ON W.[wbs] = Z.wbs AND W.cost_element_id = Z.cost_element_id  
                             WHERE   
                             W.[wbs] = A.[wbs] AND W.[cost_element_id] = A.[cost_element_id])  
                  AS amount

Accepted Answer

Hi @Mark Stokes ,

Welcome to Microsoft Q&A!

You could just use avg function with group by which could average the value according to the count of rows in one group.

Please refer below and check whether it is helpful to you:

INSERT INTO @virtual_table_averages  
SELECT subfunction_id,wbs,cost_element_id,AVG(AMOUNT) AMOUNT  
 FROM   
 (  
 SELECT subfunction_id,wbs,cost_element_id,AMOUNT FROM @virtual_table1  
UNION ALL  
SELECT subfunction_id,wbs,cost_element_id,AMOUNT FROM @virtual_table2    
UNION ALL  
SELECT subfunction_id,wbs,cost_element_id,AMOUNT FROM @virtual_table3  
) A  
 GROUP BY subfunction_id,wbs,cost_element_id  
  
 Select * from @virtual_table_averages

Output:

fin_transactions_id	subfunction_id	wbs	cost_element_id	amount  
1	1	wbs-1	1	5.0000  
2	1	wbs-1	2	16.6667  
3	1	wbs-1	3	10.0000  
4	1	wbs-2	1	5.0000  
5	1	wbs-2	3	10.0000  
6	1	wbs-2	4	10.0000  
7	1	wbs-2	5	10.0000  
8	1	wbs-3	1	5.0000  
9	1	wbs-3	4	10.0000  
10	1	wbs-3	5	10.0000

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Union All and average matching columns

0 additional answers

Your answer