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