SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [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?
    Thank you in advance.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •