December 19, 2013 at 12:14 am
Hi Friends!
How can i add and subtract different rows in database, again mentioning not adding all of rows but some of rows from the database?
e.g
I have five rows in a table and i want to add row 1,3,5 and 2,4 and then subtract first result from second like (1+3+5)-(2+4)?
How can i do this in sql?
December 19, 2013 at 2:40 am
Interesting question, so I got to spend some time on it 🙂
This is one example:
create table t1
(id int identity(1,1),
a int,
b int,
c int)
insert into t1(a,b,c)
values (10,20,30),(20,30,40),(30,40,50),(40,50,60),(50,60,70)
select * from t1
select (a1.sum_a - a2.sum_a) [diff_a], (a1.sum_b - a2.sum_b) [diff_b], (a1.sum_c - a2.sum_c) [diff_c]
from
(
select 1 as rowId,sum(a) as [sum_a],sum(b) as [sum_b],sum(c) as [sum_c]
from t1
where id % 2 = 1 --odd rows
) as a1
join
(
select 1 as rowId,sum(a) as [sum_a],sum(b) as [sum_b],sum(c) as [sum_c]
from t1
where id % 2 = 0 --even rows
) as a2
on a1.rowId = a2.rowId
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
December 19, 2013 at 2:45 am
My take on it. Prob better ways to get the result required
DECLARE @TempData TABLE ( tdID BIGINT IDENTITY(1,1),anumber BIGINT )
INSERT INTO @TempData
( anumber )
VALUES ( 1 ),
( 3 ),
( 5 ),
( 9 ),
( 4 );
WITH RowNumberData
AS ( SELECT anumber ,
ROW_NUMBER() OVER ( ORDER BY tdID ) AS RowNum
FROM @TempData
),
FirstSum
AS ( SELECT SUM(rnd1.anumber) AS Sum1
FROM RowNumberData rnd1
WHERE rnd1.RowNum IN ( 1, 3, 5 )
),
SecondSum
AS ( SELECT SUM(rnd1.anumber) AS Sum2
FROM RowNumberData rnd1
WHERE rnd1.RowNum IN ( 2, 4 )
)
SELECT Sum1 ,
sum2 ,
sum1 - sum2 AS Result
FROM FirstSum
CROSS APPLY SecondSum
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply