# [SQL Server 2005] Problem with sum in query

• Sep 5, 2012, 02:00
cms9651
[SQL Server 2005] Problem with sum in query
Hi there, I hope your help.

This is my SQL server table:
Code:

```MAT        ENER1        ENER2        ENER3        ENER4        COD1        COD2        COD3        COD4        d        150        25        35        125        300        303        305        307 d        100        15        40        110        300        303        305        311 d        130        17        28        63        305        312        313        313```
With query I need:
1. Sum ENER1 when COD1 is in 300, 303, 305, 307, 309, 311, 313, 315;
2. Sum ENER2 when COD2 is in 300, 303, 305, 307, 309, 311, 313, 315;
3. Sum ENER3 when COD3 is in 300, 303, 305, 307, 309, 311, 313, 315;
4. Sum ENER4 when COD4 is in 300, 303, 305, 307, 309, 311, 313, 315;
5. General total [ENER1] + [ENER2] + [ENER3] + [ENER4].

Output:
Code:

```MAT        ENER1        ENER2        ENER3        ENER4        TOTAL d        380        40        103        298        821```
Can you help me?
```select MAT,       Ener1,       Ener2,       Ener3,       Ener4,       Ener1 + Ener2 + Ener3 + Ener4 as Total   from (select MAT,               sum(case when COD1 in (300, 303, 305, 307, 309, 311, 313, 315)                   then ENER1 else 0 end) as Ener1,               sum(case when COD2 in (300, 303, 305, 307, 309, 311, 313, 315)                   then ENER2 else 0 end) as Ener2,               sum(case when COD3 in (300, 303, 305, 307, 309, 311, 313, 315)                   then ENER3 else 0 end) as Ener3,               sum(case when COD4 in (300, 303, 305, 307, 309, 311, 313, 315)                   then ENER4 else 0 end) as Ener4           from mySQLServerTable         group             by MAT) dt```