group by 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

SQL Server 2005 Forums

Transact-SQL (2005)

group by problem

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2010-11-10 : 06:29:09

I need to do a SUM using a GROUP BY. But the problem is - to ignore one of the group by fields and add what would have been his SUM result to among the other fields.
Here is a test example with values for 3 colors. I would like to spread the values for Blue amongst the other two colors.

CREATE TABLE [dbo].[aTestTable](
[Color] [nchar](10) NULL,
[Value] [int] NULL
) ON [PRIMARY]

INSERT INTO aTestTable
([Color],[Value])

Select 'Red',2
UNION ALL
Select 'Red',3
UNION ALL
Select 'Green',1
UNION ALL
Select 'Green',3
UNION ALL
Select 'Blue',5
UNION ALL
Select 'Blue',5

So the result of:
select SUM([value]) as Total,Color from aTestTable
Group by color
Gives: Red:5 , Green:4 , Blue:10
But spreading Blues out should give Red:10, Green:9

Any help appreciated.

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 06:33:15

If I understand your requirement correctly shouldn't Red be 5 and Green be 5 if Blue is 10.

PBUH

lappin
Posting Yak Master

182 Posts

Posted - 2010-11-10 : 06:44:37

Using the example above the sum result is: Red:5 (2+3), Green:4 (1+3), Blue:10 (5+5)
Spreading Blues total (10) out among the other two colors we need to give 10/2 to the others. So Red:10 (2+3+5), Green:9 (1+3+5)

Humate
Posting Yak Master

101 Posts

Posted - 2010-11-10 : 06:45:30

Not dynamic, but a starter:

select SUM([value]) as Total, Color, t1.Total / COUNT(*) AS Difference, SUM([value]) + t1.Total / COUNT(*) as TotalNew
from aTestTable

cross join(
select SUM([value]) as Total
from aTestTable
where Color = 'blue') as t1

where color <> 'blue'
group by color, t1.Total

lappin
Posting Yak Master

182 Posts

Posted - 2010-11-10 : 06:51:47

Excellent thank you. This worked great on the test table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources