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.
This question is related to
sql
sql-server
sql-server-2005
Hi You can use a simple query,
select emp_cd, val1, val2, val3,
(val1+val2+val3) as total
from emp;
In case you need to insert a new row,
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
Just as a regular SELECT
?
SELECT
Val1, Val2, Val3,
Total = Val1 + Val2 + Val3
FROM dbo.Emp
Or do you want to determine that total and update the table with those values?
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
Then you will always get the current total - even if the values change:
SELECT
Val1, Val2, Val3, CurrentTotal
FROM dbo.Emp
Try this:
select sum(num_tax_amount+num_total_amount) from table_name;
SELECT Emp_cd, Val1, Val2, Val3, SUM(Val1 + Val2 + Val3) AS TOTAL
FROM Emp
GROUP BY Emp_cd, Val1, Val2, Val3
You must also be aware of null
records:
SELECT (ISNULL(Val1,0) + ISNULL(Val2,0) + ISNULL(Val3,0)) as 'Total'
FROM Emp
Usage of ISNULL
:
ISNULL(col_Name, replace value)
Another example using 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
use a trigges it will work:-
->CREATE TRIGGER trigger_name BEFORE INSERT ON table_name
FOR EACH ROW SET NEW.column_name3 = NEW.column_name1 + NEW.column_name2;
this will only work only when you will insert a row in table not when you will be updating your table for such a pupose create another trigger of different name and use UPDATE on the place of INSERT in the above syntax
Source: Stackoverflow.com