How do I do SQL sum across a table diagonally?

I have this table:

Quarter | Col A | Col B | Col C | Col D | Col E
--------+-------+-------+-------+-------+-------
1       | aaa   | 1     | 2     | 3     | 4
2       | aaa   | 1.1   | 2.2   | 3.3   | 4.4
3       | aaa   | 1.11  | 2.22  | 3.33  | 4.44
4       | aaa   | 1.111 | 2.222 | 3.333 | 4.444
1       | bbb   | 5     | 6     | 7     | 8
2       | bbb   | 5.5   | 6.6   | 7.7   | 8.8
3       | bbb   | 5.55  | 6.66  | 7.77  | 8.88
4       | bbb   | 5.555 | 6.666 | 7.777 | 8.888

I want to get this:

Col A | Sum_Diagonally_Across_Quarters
------+-------------------------------
aaa   | 1 + 2.2 + 3.33 + 4.444
bbb   | 5 + 6.6 + 7.77 + 8.888

The idea is to sum up the Col B of Quarter 1 with Col C of Quarter 2… and group by Col A

Answer

Very strange, but you can use conditional aggregation:

select cola,
       sum(case when quarter = 1 then colb
                when quarter = 2 then colc
                when quarter = 3 then cold
                when quarter = 4 then cole
           end)                
from t
group by cola

Advertisement

Answer