Calculating Horizontal Totals in Pivot in MS SQL Server 2008

Question

I want to know how to calculate Row subtotals.

My table looks like this:

FCode   DMAR15  DMAR02  DMAR13  DMAR06
F83006     292     334     111     152
F83025     272     298      80     140
F83048     166     179      56      57
F83049      27      32      15      17
F83050     105     112      45      53
F83635     139     153      41      41

My script is:

SELECT [FCode],
       [DMAR15],
       [DMAR02],
       [DMAR13],
       [DMAR06],
       [PCVDR41],
       [PCVDR42],
       [CLDP031],
       [CLDP003],
       [CLDP012],
       [CLDP028],
       [CLDP023],
       [CLDP021],
       [CLDP016],
       [CLDP022]
FROM   (SELECT [FCode],
               [Aggregate],
               [QName]
        FROM   [dbo].[tblMiquestResults]
        WHERE  AuditDate = '2012-09-30') AS SourceTable 
PIVOT (AVG (Aggregate) FOR [QName] IN ([DMAR15], [DMAR02], [DMAR13], 
                                       [DMAR06], [PCVDR41], [PCVDR42], 
                                       [CLDP031], [CLDP003], [CLDP012], 
                                       [CLDP028], [CLDP023], [CLDP021], 
                                       [CLDP016], [CLDP022])) AS P

I want to make it look like this:

FCode   DMAR15  DMAR02  DMAR13  DMAR06
F83006     292     334     111     152
F83025     272     298      80     140
F83048     166     179      56      57
F83049      27      32      15      17
F83050     105     112      45      53
F83635     139     153      41      41
Total     1001    1108     348     460

Can you show sample data that your query is supposed to run on? — bobs, Jan 29 '13 at 18:20
"Horizontal totals" is a little unclear; your totals run horizontally, but each total is vertically. "A total row" would be less ambiguous. — , Jan 29 '13 at 22:40
Yes, but once can total across, down, or both. bonCondigo guessed that you wanted to total across, which is why I suggest explicitly saying "a total row" instead. — , Jan 30 '13 at 00:51

score 1 · Accepted Answer · answered Jan 29 '13 at 22:55

One way of doing it

SELECT CASE WHEN GROUPING([FCode]) = 1 THEN 'Total' ELSE [FCode] END AS [FCode],
       SUM([DMAR15]) AS DMAR15,
       SUM([DMAR02]) AS [DMAR02]
        /*TODO: Rest of columns*/
FROM   (SELECT [FCode],
               [Aggregate],
               [QName]
        FROM   [tblMiquestResults]) AS SourceTable 
PIVOT (AVG (Aggregate) FOR [QName] IN ([DMAR15], [DMAR02], [DMAR13], 
                                       [DMAR06], [PCVDR41], [PCVDR42], 
                                       [CLDP031], [CLDP003], [CLDP012], 
                                       [CLDP028], [CLDP023], [CLDP021], 
                                       [CLDP016], [CLDP022])) AS P 
GROUP BY GROUPING SETS ((FCode),())

SQL Fiddle

score 0 · Answer 2 · answered Jan 29 '13 at 18:12

Well given your above table (not looking at your query) following is a query to do the rowsum.

SQLFIDDLE DEMO

select a.fcode, a.DMAR15,  a.DMAR02,  
a.DMAR13,  a.DMAR06, (a.DMAR15 +  a.DMAR02 +
a.DMAR13 +  a.DMAR06) as RowSum
from demo a
;

|  FCODE | DMAR15 | DMAR02 | DMAR13 | DMAR06 | ROWSUM |
-------------------------------------------------------
| F83006 |    292 |    334 |    111 |    152 |    889 |
| F83025 |    272 |    298 |     80 |    140 |    790 |
| F83048 |    166 |    179 |     56 |     57 |    458 |
| F83049 |     27 |     32 |     15 |     17 |     91 |
| F83050 |    105 |    112 |     45 |     53 |    315 |
| F83635 |    139 |    153 |     41 |     41 |    374 |

@KaisMalique Thsi query is very basic.. so if you could spell out the conditions that you trying to apply to your own query and expected results, then that would be great. — bonCodigo, Jan 29 '13 at 18:18
I am trying to get a horizontal subtotal for a pivot. Thanks. — Djbril, Jan 29 '13 at 22:21

Calculating Horizontal Totals in Pivot in MS SQL Server 2008

2 Answers2