Add a Summary Row with Totals

Add a summary row with totals

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)
;

SQL sum of rows to create Total row

Asumming that you r sample data is a table, you could get the totals using pure sql like this:

select CASSR_CODE, AgeBand_Key, AgeBand, [count] from your_sample_table
union all
select CASSR_CODE, 4 as AgeBand_Key, 'Total' as AgeBand, sum([count])
from your_sample_table
group by CASSR_CODE
order by CASSR_CODE, AgeBand_Key

Depending on your DBMS, there are other better choices in the links shared by @Luuk

SQL Add row of Total from a current column

try

SELECT
Rep, Monday, Tuesday, Wednesday, Thursday, Friday, Weekly_Total
FROM #bl_reptemp3

UNION

SELECT
Null Rep, Null Monday, NULL Tuesday, Null Wednesday, Null Thursday, NULL Friday, 
SUM(Weekly_Total) Weekly_Total
FROM #bl_reptemp3

Add 'total' row for each group in a column in df

library( tidyverse )
library( janitor )

dat %>%
  dplyr::arrange( size ) %>%
  split( .[,"size"] ) %>%
  purrr::map_df(., janitor::adorn_totals)

#  size category store1 store2
#     L    shirt     52      2
#     L    pants      5      6
#     L      hat     37     12
# Total        -     94     20
#     M    shirt     10     24
# Total        -     10     24
#     S    shirt     22     43
# Total        -     22     43
#    XS    shirt      3     13
#    XS    pants     21     40
# Total        -     24     53

Entity Framework 6: Add a summary row with totals

Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication99
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("ID", typeof(int));
            dt1.Columns.Add("COLOR", typeof(string));
            dt1.Columns.Add("NAME", typeof(string));

            dt1.Rows.Add(new object[] { 1, "red", "aaa"});
            dt1.Rows.Add(new object[] { 2, "red", "vvv"});
            dt1.Rows.Add(new object[] { 3, "green", "fff"});
            dt1.Rows.Add(new object[] { 4, "green", "ggg"});
            dt1.Rows.Add(new object[] { 5, "yellow", "eee"});

            DataTable dt2 = new DataTable();
            dt2.Columns.Add("COLOR", typeof(string));
            dt2.Columns.Add("COUNT", typeof(int));

            var groups = dt1.AsEnumerable().GroupBy(x => x.Field("COLOR"));
            foreach(var group in groups)
            {
                dt2.Rows.Add(new object[] {group.Key, group.Count()});
            }
            dt2.Rows.Add(new object[] { "allColor", dt1.Rows.Count });
        }
    }
}

---

Related Topics