CODEWITHSUNDEEP
sqlsql-serversql-server-2008
pyram
pyram

Reputation: 935

Get sum of column on last row, good practice?

I'm using sql server 2008. I'm doing a query which is going to be used by a SSIS.

I have this data on a select:

  Master_Code   Jan 
    1            4
    2            5

I would like to add a total on the last row like this:

  Master_Code   Jan 
    1            4
    2            5
    Total        9

Is good practice to do this from sql or should I do this total from SSIS?

Upvotes: 1

Views: 9406

Answers (2)

Serge Belov
Serge Belov

Reputation: 5803

Looking at your query it seems the data type would have to be different (int vs varchar or null perhaps) which makes it a quite questionable choice for SQL side. I mean from query re-using perspective special values are really really bad.

So ideally I'd make two different queries (one for details, one for totals), both run in SQL Server and use SSIS to union the results in a data transformation. That'd probably be the most efficient and a clean enough way.

Upvotes: 1

Taryn
Taryn

Reputation: 247720

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

Upvotes: 2

Related Questions