[SQL Server 2005] Problem with sum in query

[cms9651]

Hi there, I hope your help.

This is my SQL server table:

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:

MAT	ENER1	ENER2	ENER3	ENER4	TOTAL
d	380	40	103	298	821

Can you help me?
Thank you in advance.

[swampBoogie]

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

[cms9651]

Thank you very much!