Add a summary row with totals

Question

I know this sounds crazy and probably should not be done this way but I need something like this - I have a records from SELECT  Type    Total Sales  From Before  I want to add an extra row at the end to show the SUM at the end of the table  After   Could this be done

User · Answer

Try to use union all as below  SELECT  Type    Total Sales  From Before union all SELECT  Total   Sum  Total Sales   From Before   if you have problem with ordering  as i-one suggested try this   select  Type    Total Sales   from  SELECT  Type    Total Sales   0  Key         From Before        union all        SELECT  Total   Sum  Total Sales    1 From Before  sq  order by  Key   Type

User · Answer

If you are on SQL Server 2008 or later version  you can use the ROLLUP   GROUP BY function   SELECT   Type   ISNULL Type   Total      TotalSales   SUM TotalSales  FROM atable GROUP BY ROLLUP Type      This assumes that the Type column cannot have NULLs and so the NULL in this query would indicate the rollup row  the one with the grand total  However  if the Type column can have NULLs of its own  the more proper type of accounting for the total row would be like in  Declan K s answer  i e  using the GROUPING   function   SELECT   Type   CASE GROUPING Type  WHEN 1 THEN  Total  ELSE Type END    TotalSales   SUM TotalSales  FROM atable GROUP BY ROLLUP Type

User · Answer

If you want to display more column values without an aggregation function use GROUPING SETS instead of ROLLUP   SELECT   Type   ISNULL Type   Total      SomeIntColumn   ISNULL SomeIntColumn  0     TotalSales   SUM TotalSales  FROM atable GROUP BY GROUPING SETS   Type  SomeIntColumn        ORDER BY SomeIntColumn --Displays summary row as the first row in query result

User · Answer

You could use the ROLLUP operator  SELECT  CASE              WHEN  GROUPING  Type     1  THEN  Total              ELSE  Type  END AS  TYPE           SUM  Total Sales   as Total Sales From    Before GROUP BY          Type  WITH ROLLUP

User · Answer

This is the more powerful grouping   rollup syntax you ll want to use in SQL Server 2008   Always useful to specify the version you re using so we don t have to guess   SELECT     Type    COALESCE  Type    Total        Total Sales    SUM  Total Sales   FROM dbo Before GROUP BY GROUPING SETS   Type          Craig Freedman wrote a great blog post introducing GROUPING SETS

[sql] Add a summary row with totals

Examples related to sql

Examples related to sql-server

Examples related to rollup