June 20, 2012 at 12:09 pm
Hello, i hope you can help me.
I have the following table.
declare @table3 table(n int,p int,t float)
with the following records;
insert into @table3
select 9,2,2701 union all
select 1464,1,-142636 union all
select 1464,1,-142636 union all
select 1465,1,-89000 union all
select 1466,2,0 union all
select 1467,2,0 union all
select 1468,1,-54000 union all
select 1468,2,-333000 union all
select 1469,2,0 union all
select 1470,1,-29700 union all
select 1470,2,-50248 union all
select 1471,2,-39138 union all
select 1472,2,-473724 union all
select 1473,2,-566697 union all
select 1473,3,-59400 union all
select 1474,2,-368037 union all
select 1474,3,-9500 union all
select 1475,1,-156000 union all
select 1475,2,-126369
I need to sum every t where p = 1 only if p = 2 or p = 1 for each n.
Thanks in advance guys!
June 20, 2012 at 12:24 pm
How about:
declare @table3 table(n int,p int,t float)
insert @table3 (n,p,t)
select 1469, 2, 0
union all
select 1470, 1, -29700
union all
select 1470, 2, -50248.8
union all
select 1471, 2, -39138
union all
select 1472, 2, -473724
union all
select 1473, 2, -566697.6
union all
select 1473, 3, -59400
union all
select 1474, 2, -368037.6
union all
select 1474, 3, -9500
union all
select 1475, 1, -156000
union all
select 1475, 2, -126369
select [n], sum(case [P] when 1 then [T]
else 0
end) as [t]
from @table3 where n in ( select distinct [N] from @table3 where p in (1,2))
group by [n]
June 20, 2012 at 12:39 pm
Thanks for the reply david but it didn´t come out what i expected.
For the record i´ve managerd to get the results as i want but i think that maybe it´s not the most effcient way to do it.
Run the code as it is, may be the code can explain my problem better than i did :-D;
declare @table3 table(n int,p int,t float)
insert into @table3
select 9,2,2701 union all
select 1464,1,-142636 union all
select 1465,1,-89000 union all
select 1466,2,0 union all
select 1467,2,0 union all
select 1468,1,-54000 union all
select 1468,2,-333000 union all
select 1469,2,0 union all
select 1470,1,-29700 union all
select 1470,2,-50248 union all
select 1471,2,-39138 union all
select 1472,2,-473724 union all
select 1473,2,-566697 union all
select 1473,3,-59400 union all
select 1474,2,-368037 union all
select 1474,3,-9500 union all
select 1475,1,-156000 union all
select 1475,2,-126369
select * from @table3
declare @table4 table (n int, c int)
insert into @table4
select n,COUNT(n) from @table3 where p = 1 or p = 2 group by n
delete from @table4 where c = 1
select * from @table4
select SUM(a.t),p
from @table3 a
join @table4 b on a.n = b.n
group by p
June 20, 2012 at 2:04 pm
select SUM(a.t),p
from @table3 a
where exists
(
select n
from @table3 t
where (p = 1 or p = 2)
and t.n = a.n
group by n
having count(*)<>1
)
group by p
June 20, 2012 at 2:08 pm
Alexander Suprun (6/20/2012)
select SUM(a.t),pfrom @table3 a
where exists
(
select n
from @table3 t
where (p = 1 or p = 2)
and t.n = a.n
group by n
having count(*)<>1
)
group by p
that´s perfect!!! thanks,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply