Need a horizontal sum - 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

Need a horizontal sum

Author

Topic

pgmr1998
Yak Posting Veteran

66 Posts

Posted - 2008-09-18 : 16:38:53

I have a field that contains one of three values for each record.
Within a division, I want to have all possible values from this one field appear on a single output record, rather than on a record for each value.
Example:

Select div, fielda, fieldb from taxroles

OUTPUT

div fielda fieldb
01 A 05
01 A 10
01 B 30
02 B 15
I WOULD LIKE TO SEE THIS:

div sum(A) sum(B)
01 15 30
02 00 15

NOTE- I am trying to get a sum on a horizontal line rather than appearing vertically on many lines. How do I do this?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-18 : 16:49:53

select div,sum(case when fielda in ('A') and fieldb <=10 then fieldb else '0' end)as SumA,sum(case when fielda in ('B') and
fieldb between 15 and 30 then fieldb else '00' end)as SUMB
from your table
group by div

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-18 : 16:52:54

Here is a similar way:


declare @yourTable table
(div int, fielda char(1), fieldb int)
insert @yourTable
select 01, 'A', 05 union all
select 01, 'A', 10 union all
select 01, 'B', 30 union all
select 02, 'B', 15


select div
       ,sum(case when fielda = 'A' then fieldb else 0 end) as [Sum(A)]
       ,sum(case when fielda = 'B' then fieldb else 0 end) as [Sum(B)]
from   @yourTable
group by div

output:
div         Sum(A)      Sum(B)
----------- ----------- -----------
1           15          30
2           0           15

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources