How to Get the Sum of All Column Values in the Last Row of a Resultset

How to get the Sum of all column values in the last row of a resultset?

Assuming there are no null master_code rows.

SELECT ISNULL(Master_code, 'Total') AS Master_Code,
       Jan,
       Feb,
       Mar
FROM (
      SELECT Master_code,
             SUM(Jan) AS Jan,
             SUM(Feb) AS Feb,
             SUM(Mar) AS Mar
      FROM foobar
      WHERE Participating_City = 'foofoo'
      GROUP BY Master_code WITH ROLLUP
     ) AS DT

SQL sum total each column in last row

Generally speaking, you want to leave totals and sub-totals to whatever tool you are presenting your data in, as they will be able to handle the formatting with significantly more ease. In addition, your desired output does not have the same number of columns (Grand Total row only has one numeric) so even if you did shoehorn this in to the same dataset, the column headings wouldn't make sense.

That said, you can return group totals via the with rollup statement. This will provide an additional row with the aggregate totals for the group. Where there is more than one group in your data, you will get a sub-total row for each group and a total row for the entire dataset:

declare @t table(c nvarchar(10),t nvarchar(3));
insert into @t values ('D173','IPO'),('D176','IPO'),('D176','IPO'),('D176','IPO'),('D184','IPO'),('D184','UOR'),('D185B','IPO'),('D187','IPO'),('D187','UOR'),('D187','UOR'),('F042','IPO'),('F042','IPO'),('F042','IPO'),('TTPMC','IPO'),('TTPMC','IPO'),('Z00204','IPO'),('ML004','UOR'),('ML004','UOR'),('ML004','UOR'),('ML004','IPO'),('ML004','IPO'),('ML004','IPO'),('ML004','IPO'),('ML004','IPO'),('ML004','IPO'),('ML004','IPO'),('ML004','IPO'),('ML004','IPO'),('ML004','IPO'),('ML004','IPO'),('ML004','IPO');

select row_number() over (order by grouping(c),c) as n
    ,case when grouping(c) = 1 then 'TOTAL (NOS)' else c end as c
    ,sum(case when t = 'IPO' then 1 else 0 end) as IPO
    ,sum(case when t = 'UOR' then 1 else 0 end) as UOR
from @t
group by c
with rollup
order by grouping(c)
        ,c;

Output:

+----+-------------+-----+-----+
| n  |      c      | IPO | UOR |
+----+-------------+-----+-----+
|  1 | D173        |   1 |   0 |
|  2 | D176        |   3 |   0 |
|  3 | D184        |   1 |   1 |
|  4 | D185B       |   1 |   0 |
|  5 | D187        |   1 |   2 |
|  6 | F042        |   3 |   0 |
|  7 | ML004       |  12 |   3 |
|  8 | TTPMC       |   2 |   0 |
|  9 | Z00204      |   1 |   0 |
| 10 | TOTAL (NOS) |  25 |   6 |
+----+-------------+-----+-----+

Get sum of column on last row, good practice?

You can easily do this with a UNION ALL. The key is that that master_code field must be the same data type as the string total so you will have to convert it:

select cast(master_code as varchar(10)) master_code, jan
from yourtable
union all
select 'Total', sum(jan)
from yourtable

See SQL Fiddle with Demo

Or you can use GROUP BY with ROLLUP:

select 
  case 
    when master_code is not null 
    then cast(master_code as varchar(10)) else 'total' end master_code, 
  sum(jan) Jan
from yourtable
group by master_code with rollup

See SQL Fiddle with Demo

Get the summation a specific column and a last value of a specific column in each group in a result of SQL query using SQL Server

Finally I solved this issue, it was my mistake in reading and comparing between the data in tables and in results.
This mistake caused by the behavior of LAST function in the MS Access.

I'm really sorry to annoying you guys about this.

how to get the summation of column in the last row

It is done via the grouping sets like:

DECLARE @t TABLE ( code CHAR(3), a INT, b INT )

INSERT  INTO @t
VALUES  ( 'USD', 1, 2 ),
        ( 'USD', 5, 1 ),
        ( 'USD', 10, 7 ),
        ( 'EUR', 15, 13 )

SELECT  code ,
        SUM(a) AS a ,
        SUM(b) AS b
FROM    @t
GROUP BY GROUPING SETS(( code ), ( ))

Output:

code    a   b
EUR     15  13
USD     16  10
NULL    31  23

---

Related Topics