multiple sum in a single line - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

multiple sum in a single line

Author

Topic

shagil.a.gopinath
Starting Member

14 Posts

Posted - 2010-04-25 : 07:33:45

Hi,

This is my requirement , from below table i need to get the sum of the value in a single line and the condition is data value is greater than 20(Sum2) and data value is lesser than 20(Sum1).

pk value data category

a 10 15 AA
b 20 19 AA
c 15 21 CC
d 10 22 CC

output must be like this

sum1 sum2

30 25

Can i get this output using select query with out using store procedure or cursor

Please help me

Thanks & Regards

shagil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-25 : 07:36:28

select sum(case when category = 'aa' then value else 0 end) as sum1,
sum(case when category = 'cc' then value else 0 end) as sum2
from yourtablenamehere

N 56°04'39.26"
E 12°55'05.63"

shagil.a.gopinath
Starting Member

14 Posts

Posted - 2010-04-25 : 07:43:34

it works fine, thanks for your help Peso

Thanks & Regards

shagil

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-25 : 07:55:48

try this also

create nonclustered index ix_0 on table_name (category) include (value)

;with c1 as
(select sum(value) as sum1, k=1
from table_name
where category = 'aa'),
c2 as
(select sum(value) as sum2, k=1
from table_name
where category = 'cc')
select sum1, sum2
from c1
inner join c2
on c1.k = c2.k;

shagil.a.gopinath
Starting Member

14 Posts

Posted - 2010-04-25 : 08:24:12

Hi,

I have tried , its also working fine. anyway i could learn two method for the same requirement.

Thanks a lot

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-25 : 08:38:47

Also

create function dbo.CrossTab (@category char(2)) returns table as
return
(select sum(value) as sum
from table_name
where category = @category);
GO

select c1.sum, c2.sum
from dbo.CrossTab('aa') as c1
cross join dbo.CrossTab('cc') as c2

EDIT: croos -> cross

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources