Perform aggregations with two tables with same structure

Question

I have two tables that hold the same type of data, one with current day data and one with previous day data:

Current:

CREATE TABLE Current (
    Col1 VARCHAR(50),
    Col2 VARCHAR(10), 
    Col3 VARCHAR(2), 
    Col4 DATE,       
    Col5 INT,         
    Col6 NUMERIC(5,2)
);

INSERT INTO Current (Col1, Col2, Col3, Col4, Col5, Col6)
VALUES 
('ItemA', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-07-01', 12345, 10.50),
('ItemA', CASE WHEN LEFT('oH', 1) = UPPER(LEFT('oH', 1)) THEN 'Sell' ELSE 'Buy' END, 'oH', '2025-08-01', 23456, 20.75),
('ItemB', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-07-01', 34567, 30.80),
('ItemC', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-09-01', 45678, 40.25),
('ItemC', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 56789, 50.60),
('ItemD', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-09-01', 67890, 60.10),
('ItemE', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-07-01', 78901, 70.95),
('ItemE', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 89012, 15.35);

Previous:

CREATE TABLE Previous (
    Col1 VARCHAR(50),
    Col2 VARCHAR(10), 
    Col3 VARCHAR(2), 
    Col4 DATE,       
    Col5 INT,         
    Col6 NUMERIC(5,2)
);

INSERT INTO Previous (Col1, Col2, Col3, Col4, Col5, Col6)
VALUES 
('ItemA', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-07-01', 12350, 10.55),
('ItemA', CASE WHEN LEFT('oH', 1) = UPPER(LEFT('oH', 1)) THEN 'Sell' ELSE 'Buy' END, 'oH', '2025-08-01', 23461, 20.80),
('ItemB', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-07-01', 34572, 30.85),
('ItemC', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-09-01', 45683, 40.30),
('ItemC', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 56794, 50.65),
('ItemD', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-09-01', 67905, 60.15),
('ItemE', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-07-01', 78916, 70.90),
('ItemE', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 89027, 15.40);

I run this query with the 2 tables:

SELECT Col1, SUM(Col5) AS 'sum', 'C' AS 'Flag'
FROM Current
GROUP BY Col1
UNION
SELECT Col1, SUM(Col5) AS 'sum', 'P' AS 'Flag'
FROM Previous
GROUP BY Col1
ORDER BY Col1;

which returns:

Col1    Sum    Flag
ItemA   35801   C
ItemA   35811   P
ItemB   34567   C
ItemB   34572   P
ItemC   102477  P
ItemC   102467  C
ItemD   67905   P
ItemD   67890   C
ItemE   167913  C
ItemE   167943  P

I added the flag column for clarity so you can tell where the values are coming from. I need to edit this query to return this a table like this:

Col1    Current Sum Previous Sum    Difference
ItemA   35811.          35801           10
ItemB   34572           34567            5
ItemC   102477          102467          10
ItemD   67905           67890           15
ItemE   167943          167913          30

but I can not figure out how to select the correct values to be aggregated since the tables are identical.

Edit: I should note that the tables display current day data and previous day data so it is possible for either table to contain values with no match. For instance, a row of data can “expire” and be included only in the previous table and not the current. A row can also be “added” and only be included in current and not previous. In these cases, one value would need to be filled as 0

Answer 1

I would just perform the aggregates separately and then full outer join:

WITH p AS 
(
  SELECT i = Col1, p = SUM(Col5)
  FROM Previous GROUP BY Col1
),
c AS 
(
  SELECT i = Col1, c = SUM(Col5)
  FROM [Current] GROUP BY Col1
),
s AS
(
  SELECT Item       = COALESCE(p.i, c.i),
         [Current]  = COALESCE(c.c, 0), 
         Previous   = COALESCE(p.p, 0)
  FROM p FULL OUTER JOIN c
    ON c.i = p.i
)
SELECT *, Difference = Previous - [Current] FROM s;

• db<>fiddle

Answer 2

When you select them, select them into the output column you want them in e.g. the Current query goes into the Current Sum column and the Previous query goes into the Previous Sum column.

WITH cte AS (
    SELECT Col1, Col5 [Current Sum], NULL [Previous Sum]
    FROM [Current]
    UNION ALL
    SELECT Col1, NULL, Col5
    FROM [Previous]
)
SELECT
    Col1
    , SUM([Current Sum]) [Current Sum]
    , SUM([Previous Sum]) [Previous Sum]
    , SUM([Previous Sum]) - SUM([Current Sum]) Difference
FROM cte
GROUP BY Col1
ORDER BY Col1;

Returns as requested:

Col1	Current Sum	Previous Sum	Difference
ItemA	35801	35811	10
ItemB	34567	34572	5
ItemC	102467	102477	10
ItemD	67890	67905	15
ItemE	167913	167943	30

Note 1: Don't delimit your column names with '' as its not an official delimiter and has unexpected consequences in some situations.
Note 2: You need UNION ALL not UNION because UNION removes duplicates which you definitely don't want. And it runs slower because of it.

DBFiddle