TSQL - comparing grouped values within a table

Question

I need to compare grouped data to look for shifts in a calculated value. The output of my current SQL looks something like this...

Grp_ID_1 / Metric / State / Value
A   Metric1 OH  50
B   Metric1 OH  65
A   Metric1 CA  20
B   Metric1 CA  35

In the example above, I need to calculate the difference between A-Metric1-OH value of 50 and B-metric1-OH value of 65.

doing a little research, it appears that I have to define every column in pivot. For this particular example, I would need to list all 50 states. Other comparisons are even more complex. This seems inefficient. Need a way to simply state the subgroups (metric/state) and then compare across the two groupIDs. Will always have 2 groups, base/test sort of scenario. — Mike, Oct 04 '18 at 18:13

score 0 · Accepted Answer · answered Oct 04 '18 at 17:42

0

You can use LEAD to calculate difference between rows.

SELECT LEAD(State, 1,0) OVER (ORDER BY Grp_ID_1 ) AS NextState ,
State - LEAD(State, 1,0) OVER (ORDER BY Grp_ID_1 ) AS StateDif
FROM yourTable

answered Oct 04 '18 at 17:42

Eugene

1,487
10
23

lije · Answer 2 · 2018-10-04T20:27:55.100

0

        SELECT  grp_ID_1, metric, state, value,       
                (SELECT  MAX(value) 
                    FROM tablename   
                 ) - value AS Difference

        FROM tablename  group by state, grp_ID_1, metric, value  
        having state = 'OH'

edited Oct 04 '18 at 20:27

answered Oct 04 '18 at 20:18

lije

420
2
15

1

Welcome to Stack Overflow! You may want to improve your answer by making it less tentative (don't use "try this") and adding a short explanation of the crucial part. For future readers it's not clear if a suggestion to try something was helpful or not. – Gert Arnold Oct 04 '18 at 20:26

TSQL - comparing grouped values within a table

2 Answers2