Add total at the bottom of a view data

Transact-SQL
1

i have created a view whose data is something like following:

Mismatch Field name Count Duplicates Repeats
City 30 0 0
Name 54 2 1
Amount 917 16 2
Date 516 4 1

I need to display the total at bottom of this data. This means i need to have an extra row inserted and the data should be shown as follows:

Mismatch Field name Count Duplicates Repeats
City 30 0 0
Name 54 2 1
Amount 917 16 2
Date 516 4 1
Total 1517 22 4

Please help me with it, how shall i proceed.

2

Not sure this is what you're looking for but SQL Server has WITH ROLLUP that gives you group totals. https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

3

For better and more help responses post create table script with inserts.

4

Thanks jotorre_riversidedpss.org. But i am looking for something which can sum up vertically, corresponding to the data in sql rows.

5

Do a union query.

6

Help us help you in the future. Take the time to make some readily consumable data for your posts. Like this...

 CREATE TABLE #PretendView
        (
         [Mismatch Field Name] VARCHAR(10)
        ,[Count]    INT
        ,Duplicates INT
        ,Repeats    INT
        ,
        )
;
 INSERT INTO #PretendView
        ([Mismatch Field Name], [Count], Duplicates, Repeats)
 VALUES  ('City'  , 30, 0,0)
        ,('Name'  , 54, 2,1)
        ,('Amount',917,16,2)
        ,('Date'  ,516, 4,1)
;

Using that data, the following will do what you wish even if the MMFN column has a NULL in it.

 SELECT [Mismatch Field Name] = CASE 
                                WHEN GROUPING([Mismatch Field Name]) = 0 
                                THEN [Mismatch Field Name] 
                                ELSE 'Total' 
                                END
        ,[Count]    = SUM([Count])
        ,Duplicates = SUM(Duplicates)
        ,Repeats    = SUM(Repeats)
   FROM #PretendView
  GROUP BY [Mismatch Field Name] WITH ROLLUP
 ORDER BY GROUPING([Mismatch Field Name]), [Mismatch Field Name]
;

That produces the following...

Mismatch Field Name Count       Duplicates  Repeats
------------------- ----------- ----------- -----------
Amount              917         16          2
City                30          0           0
Date                516         4           1
Name                54          2           1
Total               1517        22          4

(5 row(s) affected)

Please see the following article for more information on the power of GROUP BY and all the crazy groupings you can get out of it.

2 Likes