行和列的总和SQL

I have a table like below.

我有一张如下表。

Year          AccountGroupA   AccountGroup B   RowSum
2004                 15                 23       38
2005                 10                 10       20
2006                  8                 15       23
2007                 16                 14       30
ColumnSum            49                 62      111

I need grand sum of rows and colums ex:- I need result as 111 in sql query.

我需要行和列的大量ex: - 我在sql查询中需要结果为111。

The Result needs to be returned from a sql function so ideally should be a single query.

结果需要从sql函数返回,因此理想情况下应该是单个查询。

3 个解决方案

#1

Something like this:

像这样的东西:

select year, 
       sum(accountGroupA) as accountGroupA, 
       sum(AccountGroupB)as accountGroupb, 
       sum(accountGroupA + AccountGroupB) as rowsum
from the_table
group by year with rollup

This assumes that you only have one row per year or if you have more than one that you actually want to group by year. If neither is the case, you can use a union to achieve the same:

这假定您每年只有一行,或者如果您有多个实际上想要按年分组的行。如果不是这种情况,您可以使用联合来实现相同的目标:

select year, accountGroupA, AccountGroupB, rowsum
from (
  select year, accountGroupA, AccountGroupB, accountGroupA + AccountGroupB as rowsum, 0 as union_order
  from the_table
  union all
  select null, sum(accountGroupA), sum(AccountGroupB), sum(accountGroupA + AccountGroupB), 1 
  from the_table
) t
order by union_order, year

SQLFiddle example: http://sqlfiddle.com/#!6/bd4bc/2

SQLFiddle示例:http://sqlfiddle.com/#!6/bd4bc/2

#2

For the ColumnSum the SQL is easy:

对于ColumnSum,SQL很简单:

SELECT sum(AccountGroupA), sum(AccountGroupB) from YOUR_TABLE

The row sums have to be coded like this:

行总和必须像这样编码:

SELECT (AccountGroupA + AccountGroupB) as RowSum from YOUR_TABLE

Forget about getting the desired result in one SQL statment. Although that is certainly possible with a nasty nested SQL I would not recommend it.

忘记在一个SQL语句中获得所需的结果。虽然使用讨厌的嵌套SQL肯定是可行的,但我不推荐它。

#3

Look at using the ROLLUP Operator.

看看使用ROLLUP运算符。

MSDN Reference: http://technet.microsoft.com/en-us/library/ms189305(v=sql.90).aspx

MSDN参考:http://technet.microsoft.com/en-us/library/ms189305(v = sql.90).aspx

I've created a SQL fiddle here: http://sqlfiddle.com/#!6/df41d/7

我在这里创建了一个SQL小提琴:http://sqlfiddle.com/#!6/df41d / 7

More specifically, the code is here:

更具体地说,代码在这里:

select 
   case when (Grouping(Year)) = 1 
         then 'ColumnSum' 
         else isnull(Year, 'Unknown') end as Year,
   sum(AccountGroupA) as AccountGroupA,
   sum(AccountGroupB) as AccountGroupB,
   sum(AccountGroupA + AccountGroupB) as RowSum
from TestTable
  group by Year with ROLLUP

#1