none
Query to Summarize

    Question

  • Hi All,

    I have a table with detailed records in a table.

    For example,

    col1 col2 col3 col4 benefit
     
    1236  30  Peter Samuel 40.00
    1236  30  Peter Samuel 50.00

    I want to summarize the single value col1 whose one single records 1236 and sum of the benefit like 90.00

    This should be applied for the entire table.  Also, I need all the columns in the result set.

    Any help!!!

    Regards,
    kccrga

    Tuesday, September 24, 2013 10:45 PM

Answers

  • You may use OVER clause to aggregate (SUM) -

    SELECT DISTINCT Col1
        ,Col2
        ,Col3 
        ,Col4 
    	,SUM(benefit) OVER (PARTITION BY Col1) AS TotalBenefi
    FROM @tab

    Narsimha


    Tuesday, September 24, 2013 10:56 PM
  • You'll have to make a choice what you want for col3 en col4. Here is one solution:

    SELECT col1,
           col2,
           MIN(col3) AS col3,
           MIN(col4) AS col4,
           SUM(benefit) AS total_benefits
    FROM my_table
    GROUP BY col1, col2
    
    Naarashimha's solution makes a different choice about those columns (or rather: rows where those columns have different values).


    Gert-Jan

    Wednesday, September 25, 2013 9:25 AM

All replies

  • You may use OVER clause to aggregate (SUM) -

    SELECT DISTINCT Col1
        ,Col2
        ,Col3 
        ,Col4 
    	,SUM(benefit) OVER (PARTITION BY Col1) AS TotalBenefi
    FROM @tab

    Narsimha


    Tuesday, September 24, 2013 10:56 PM
  • You'll have to make a choice what you want for col3 en col4. Here is one solution:

    SELECT col1,
           col2,
           MIN(col3) AS col3,
           MIN(col4) AS col4,
           SUM(benefit) AS total_benefits
    FROM my_table
    GROUP BY col1, col2
    
    Naarashimha's solution makes a different choice about those columns (or rather: rows where those columns have different values).


    Gert-Jan

    Wednesday, September 25, 2013 9:25 AM