Query to get more column using group by

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query to get more column using group by

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-05-26 : 08:56:11

I have one table with following data

Col1 Col2 Col3 BDValue
abc def INDI 5
jkl kif INDI 3
tey yui RUSS 3
vmn ert JPAN 3
xcb gth RUSS 1
mop ikl RUSS 3

Need output as follows
-------------------------

Col1 Col2 Col3 BDValue
abc def INDI 8
jkl kif INDI 8
tey yui RUSS 4
vmn ert JPAN 3
xcb gth RUSS 4
mop ikl RUSS 4

If Col3 Value is same, add the unique values of BDValue and assign to all the rows of that name. Ex: If Col3 contains value as 'INDI', then need to add unique values(5+3) and assign '8' to all rows having value as 'INDI". For 'RUSS', we have 3 values but 2 unique (3+1) as 4 to all rows. How to write query for this.

G. Satish

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-26 : 09:00:11

select t.col1, t.col2, t.col3, a.BDValue
from tbl t
join
(select col3, BDValue = sum(BDValue) from
(select distinct col3, BDValue from tbl) a
group by col3) a
on t.col3 = a.col3

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 12:25:32

[code]SELECT t.Col1, t.Col2, t.Col3, t1.BDValue
FROM Table t
CROSS APPLY (SELECT SUM(DISTINCT BDValue) AS BDValue
FROM Table
WHERE Col3=t.Col3) t1[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources