How to sum rows according to partition in SQL?

Question

how can you add values within rows according to it's partition and the sum will be placed below each partition (new row/s below each partition will be the container of the sum)?

use the link below: http://sqlfiddle.com/#!3/4e3e80/4

column names: Premium, Commission, NetPremium

    col1    col2   col3

data

    124    213    producer
     -1     12    producer

data2

    -1    312    producer
    444    -555  producer
    100    555   producer

---

    col1    col2   col3

data

    124    213    producer
     -1     12
    123    225    null     <<<<'result'

data2

    -1    312    producer
    444    -555  
    100    555
    543    312   null      <<<<'result'

Answer 1

Another way is to UNION the total rows to the raw data and order the result in the desired way, directly with the data from the fiddle linked is

SELECT 
    (CASE rno WHEN 1 THEN EndorsementId ELSE '' END )AS col1,
    *
FROM
(                   
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY EndorsementId 
                              ORDER BY PolicyNumber) AS rno,
        *
    FROM [endorsement]
    UNION ALL
    SELECT NULL As rno
         , EndorsementId, PolicyNumber, InsuredName, EffectiveDate
         , ExpirationDate, EndorsementNumber, EffectFromDate, DueDate, Producer
         , Sum(Premium) Premium
         , Sum(Commission) Commission
         , Sum(NetPremium) NetPremium
    From   [endorsement]
    Group By EndorsementId, PolicyNumber, InsuredName, EffectiveDate
           , ExpirationDate, EndorsementNumber, EffectFromDate, DueDate, Producer
) As temp1
Order By EndorsementId, Coalesce(rno, 999)

Answer 2

Use ROLLUP:

Here is a set with a few columns (Premium only)

SELECT 
    (CASE rno WHEN 1 THEN EndorsementId ELSE '' END )AS col1,
    rno,
    sum(PREMIUM)
FROM
(                   
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY EndorsementId ORDER BY PolicyNumber) AS rno,
        *
    FROM [endorsement]
) As temp1
GROUP by EndorsementId, rno
with rollup

And the result:

|  COL1 |    RNO | COLUMN_2 |
|-------|--------|----------|
| 13519 |      1 |      750 |
|       |      2 |        0 |
|       |      3 |        0 |
|       | (null) |      750 |
| 13524 |      1 |      157 |
|       |      2 |        0 |
|       |      3 |      158 |
|       |      4 |       16 |
|       | (null) |      331 |
. . . . . . . 

SQL Fiddle

BTW, the same result can be achieved with Compute BY (it just does not work in the SQL Fiddle):

SELECT 
    (CASE rno WHEN 1 THEN EndorsementId ELSE '' END )AS col1,
    rno,
    PREMIUM
FROM
(                   
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY EndorsementId ORDER BY PolicyNumber) AS rno,
        *
    FROM [endorsement]
) As temp1
ORDER by EndorsementId, rno
COMPUTE SUM(Premium) BY EndorsementId

If there is too much trouble for you to ignore the last row generated by ROLLUP then you can use the following statement that will generate you the desired result with group totals. It is a bit long, but does the job well. SQL Fiddle:

SELECT 
    (CASE rno WHEN 1 THEN EndorsementId  WHEN 9999999999 THEN 'Total'ELSE '' END )AS [Endorsement ID],
     (CASE rno WHEN 1 THEN PolicyNumber  ELSE ''  END )AS [Policy Number],
      (CASE rno WHEN 1 THEN InsuredName  ELSE ''  END )AS [Insured Name],
       (CASE rno WHEN 1 THEN [temp1].[EffectiveDate]  ELSE ''  END )AS [Effective Date], 
        (CASE rno WHEN 1 THEN [temp1].[ExpirationDate]  ELSE ''  END )AS [Expiration Date], 
         (CASE rno WHEN 1 THEN [temp1].[EndorsementNumber]  ELSE ''  END )AS [Endorsement Number],
          (CASE rno WHEN 1 THEN [temp1].[EffectFromDate]  ELSE ''  END )AS [EffectFrom Date],
           (CASE rno WHEN 1 THEN [temp1].[DueDate]  ELSE ''  END )AS [Due Date],
            (CASE rno WHEN 1 THEN [temp1].[Producer]  ELSE ''  END )AS [Producer],
             Premium,
              Commission,
               NetPremium AS [Net Premium]          
FROM
(                   
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY EndorsementId ORDER BY PolicyNumber) AS rno,
        *
    FROM [endorsement]
UNION ALL
    SELECT
             9999999999 AS rno,
             EndorsementId, 
             '', 
             '', 
             '', 
             '', 
             '',
             '',
             '',
             '',
             SUM(Premium) AS Premium,
             SUM(Commission) AS Commission,
             SUM(NetPremium) AS NetPremium
    FROM [endorsement]
    GROUP BY EndorsementId
) As temp1
ORDER BY EndorsementId, rno