simple sql problem - 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

simple sql problem

Author

Topic

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-05 : 00:34:19

I have this table:

Program lob attributes sum
Globe Globe Globe1 2
Voyager Chat Possible connection rules 1
Voyager Chat TC string information 3
Voyager Chat Refund arrangement screens 3
Voyager Chat Supplementary information 2
Voyager Phone Refund arrangement screens 1

I need another column that would look like this:

Program lob attributes sum sum per lob
Globe Globe Globe1 2 2
Voyager Chat Possible connection rules 1 9
Voyager Chat TC string information 3 9
Voyager Chat Refund arrangement screens 3 9
Voyager Chat Supplementary information 2 3
Voyager Phone Refund arrangement screens 1 3

here's my statement:

Select program, lob, attributes, count(attributes) as disputes

from dbo.disputeattributes
where attributes <> ' '
group by program, lob, attributes

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-05 : 00:52:39

Can u Explain Briefly and ur expected output

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-05 : 00:54:01

I want the output to be like this:

Program lob attributes sum sum per lob
Globe Globe Globe1 2 2
Voyager Chat Possible connection rules 1 9
Voyager Chat TC string information 3 9
Voyager Chat Refund arrangement screens 3 9
Voyager Chat Supplementary information 2 3
Voyager Phone Refund arrangement screens 1 3

matty
Posting Yak Master

161 Posts

Posted - 2009-03-05 : 00:56:13

How did you get the values for sum per lob ?

quote:
Originally posted by BankOfficerHere

I want the output to be like this:

Program lob attributes sum sum per lob
Globe Globe Globe1 2 2
Voyager Chat Possible connection rules 1 9
Voyager Chat TC string information 3 9
Voyager Chat Refund arrangement screens 3 9
Voyager Chat Supplementary information 2 3
Voyager Phone Refund arrangement screens 1 3

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-05 : 00:57:03

that's my problem. I need another column for sum per lob..with those results.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-05 : 01:01:37

if u use sum() for
then u will get output as
Program lob attributes sum sum per lob
Globe Globe Globe1 2 2
Voyager Chat Possible connection rules 1 9
Voyager Chat TC string information 3 9
Voyager Chat Refund arrangement screens 3 9
Voyager Chat Supplementary information 2 9
Voyager Phone Refund arrangement screens 1 1

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-05 : 01:03:00

yes, how can i get my desired results?

matty
Posting Yak Master

161 Posts

Posted - 2009-03-05 : 01:05:09

Is this you are looking for?
declare @Sample TABLE
(
Program varchar(50),
lob varchar(50),
attributes varchar(50),
sum int
)
insert @Sample
select 'Globe', 'Globe', 'Globe1', 2
union all
select 'Voyager', 'Chat', 'Possible connection rules', 1
union all
select 'Voyager', 'Chat', 'TC string information', 3
union all
select 'Voyager', 'Chat', 'Refund arrangement screens', 3
union all
select 'Voyager', 'Chat', 'Supplementary information', 2
union all
select 'Voyager', 'Phone', 'Refund arrangement screens', 1

SELECT program, s.lob, attributes,sum,SumPerLob FROM
@Sample s
JOIN
(
Select lob, sum(sum) as SumPerLob
from @sample
where attributes <> ' '
group by lob
)t
ON s.lob=t.lob

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-05 : 01:05:24

ur question is not clear

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-05 : 01:05:24

Are u looking for this one:--

declare @temp table (Program varchar(32),lob varchar(32), attributes varchar(32), sums int)
insert into @temp
select 'Globe' ,'Globe' ,'Globe1', 2 union all
select 'Voyager' ,'Chat', 'Possible connection rules', 1 union all
select 'Voyager' ,'Chat', 'TC strin information', 3 union all
select 'Voyager' ,'Chat' ,'Refund arrangement screens', 3 union all
select 'Voyager' ,'Chat' ,'Supplementary information', 2 union all
select 'Voyager' ,'Phone' ,'Refund arrangement screens' ,1

select t.Program,t.lob,attributes,sums,sumperlob from @temp t
left join ( select lob,sum(sums) sumperlob from @temp group by lob) t1 on t.lob = t1.lob

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-03-05 : 01:10:33

with d as
(
select 'Globe' as 'Program','Globe' as 'lob','Globe1' as 'attributes', 2 as 'sum1' union all
select 'Voyager', 'Chat', 'Possible connection rules',1 union all
select 'Voyager', 'Chat', 'TCstring information',3 union all
select 'Voyager', 'Chat', 'Refund arrangement screens',3 union all
select 'Voyager', 'Chat', 'Supplementary information',2 union all
select 'Voyage'r, 'Phone', 'Refund arrangement screens',1
)
select *,sum(sum1) over(partition by lob) from d order by lob

isk

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2009-03-05 : 02:28:11

that's not possible because I want the attributes to be dynamica

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources