How to find sum of multiple columns in a table in SQL Server 2005?

I have a table Emp which has these rows:

Emp_cd | Val1  | Val2  | Val3  | Total
-------+-------+-------+-------+-------
1     | 1.23  | 2.23  | 3.43  |
2     | 23.03 | 12.23 | 2.92  |
3     | 7.23  | 9.05  | 13.43 |
4     | 03.21 | 78.23 | 9.43  |

I want to find SUM of Val1, Val2, Val3 and which will show in the Total column.

506636 次浏览
小开

就像普通的 SELECT？

SELECT
Val1, Val2, Val3,
Total = Val1 + Val2 + Val3
FROM dbo.Emp

还是要确定总数并用这些值更新表？

UPDATE dbo.Emp
SET Total = Val1 + Val2 + Val3

If you want to have this total be current at all times - you should have a computed column in your table:

ALTER TABLE dbo.Emp
ADD CurrentTotal AS Val1 + Val2 + Val3 PERSISTED

然后你会得到 一直都是的当前总数——即使值发生了变化:

SELECT
Val1, Val2, Val3, CurrentTotal
FROM dbo.Emp
小开
最佳答案

简单:

SELECT
Val1,
Val2,
Val3,
(Val1 + Val2 + Val3) as 'Total'
FROM Emp

或者你只想要一行:

SELECT
SUM(Val1) as 'Val1',
SUM(Val2) as 'Val2',
SUM(Val3) as 'Val3',
(SUM(Val1) + SUM(Val2) + SUM(Val3)) as 'Total'
FROM Emp
小开

You must also be aware of null records:

SELECT  (ISNULL(Val1,0) + ISNULL(Val2,0) + ISNULL(Val3,0)) as 'Total'
FROM Emp

ISNULL的用法:

ISNULL(col_Name, replace value)
小开 
SELECT Emp_cd, Val1, Val2, Val3, SUM(Val1 + Val2 + Val3) AS TOTAL
FROM Emp
GROUP BY Emp_cd, Val1, Val2, Val3
小开

另一个使用 COALESCE.http://sqlmag.com/t-sql/coalesce-vs-isnull的例子

SELECT (COALESCE(SUM(val1),0) + COALESCE(SUM(val2), 0)
+ COALESCE(SUM(val3), 0) + COALESCE(SUM(val4), 0)) AS 'TOTAL'
FROM Emp
小开

使用触发器它将工作:-

->CREATE TRIGGER trigger_name BEFORE INSERT ON table_name

FOR EACH ROW SET NEW.column _ name3 = NEW.column _ name1 + NEW.column _ name2;

这将只有当你将 insert a row在表中时才会工作，而不是当你将更新你的表为这样一个目的 创建另一个不同名称的触发器，并在以上语法中 插入的位置上使用 更新

小开

嗨，你可以使用一个简单的查询,

select emp_cd, val1, val2, val3,
(val1+val2+val3) as total
from emp;

如果需要插入新行,

insert into emp select emp_cd, val1, val2, val3,
(val1+val2+val3) as total
from emp;

In order to update,

update emp set total = val1+val2+val3;

This will update for all comumns

小开

Try this:

select sum(num_tax_amount+num_total_amount) from table_name;
小开

需要对具有 null 值的 Postgres db 表进行类似的工作。最后创建了一个函数，因为有太多的 COALESCE要添加。

CREATE OR REPLACE FUNCTION array_sum_float(float[])
RETURNS float
AS
$$
DECLARE
arrInts ALIAS FOR $1;
sum int DEFAULT 0;
BEGIN
FOR I IN ARRAY_LOWER(arrInts, 1)..ARRAY_UPPER(arrInts, 1)
LOOP
sum = sum + COALESCE(arrInts[I], 0);
END LOOP;
RETURN sum;
END;
$$
LANGUAGE plpgsql;


SELECT array_sum_float(ARRAY [6,8, null, 2]);
SELECT array_sum_float(ARRAY [Val1, Val2, Val3]) from Emp;

提交答案