locked
Sum function RRS feed

  • Question

  • Hi,

    I need to be able to provide 5 Sum results from the same column, using a different variable for each sum.

    For Example SELECT SUM(TotalCost) As TotalA WHERE ProdGroup = 'A'

                       SELECT SUM(TotlaCost) AS TotalB WHERE ProdGroup = 'B'    and so on.

    I also need to drop the five totals into a table. Is there a way that I can do this?

    Any assistance would be appreciated

    Thanks

     

    Wednesday, July 21, 2010 12:10 PM

Answers

  • select  sum(case when ProdGroup = 'A' then TotalCost end) as SumA,
      sum(case when ProdGroup = 'B' then TotalCost end) as SumB
    from  sometable
    


    KH Tan
    • Marked as answer by KJian_ Friday, August 6, 2010 11:14 AM
    Wednesday, July 21, 2010 12:12 PM

All replies

  • select  sum(case when ProdGroup = 'A' then TotalCost end) as SumA,
      sum(case when ProdGroup = 'B' then TotalCost end) as SumB
    from  sometable
    


    KH Tan
    • Marked as answer by KJian_ Friday, August 6, 2010 11:14 AM
    Wednesday, July 21, 2010 12:12 PM
  • Hello,

    Try this....

    SELECT SUM(CASE WHEN ProdGroup = 'A' THEN Amount ELSE 0 END) AS SumA,
    SUM(CASE WHEN ProdGroup = 'B' THEN Amount ELSE 0 END) AS SumB,
    SUM(CASE WHEN ProdGroup = 'C' THEN Amount ELSE 0 END) AS SumC,
    SUM(CASE WHEN ProdGroup = 'D' THEN Amount ELSE 0 END) AS SumD,
    SUM(CASE WHEN ProdGroup = 'E' THEN Amount ELSE 0 END) AS SumE
    FROM TableName

    Hope its helpful....


    Pavan Kokkula Infosys Technologies Limited.
    Wednesday, July 21, 2010 12:15 PM
  • Can you show the table structure you're working with and desired output and SQL Server version? It looks like a question of a PIVOT command.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, July 21, 2010 12:15 PM
  • Hello,

    Try this

    Insert Into TBLSUMS (ProdGroup, Totals)
    SELECT ProdGroup, SUM(TotalCost) Totals FROM TBL Group By ProdGroup
    

     


    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    Wednesday, July 21, 2010 12:18 PM
  • select  sum(case when ProdGroup = 'A' then TotalCost else 0 end) as TotalCost_A,
      sum(case when ProdGroup = 'B' then TotalCost else 0 end) as TotalCost_B,
    .......... and so on

    from  test_table

     

    • Proposed as answer by Muhammad Abbas Wednesday, July 21, 2010 12:26 PM
    Wednesday, July 21, 2010 12:26 PM