# Union All and average matching columns

21 Reputation points
2021-02-11T11:16:50.81+00:00

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
``````
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,577 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,454 questions

1. 24,171 Reputation points
2021-02-12T07:33:52.06+00:00

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.

``````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