GroupBy problem when one field is not the same.

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)

GroupBy problem when one field is not the same.

Author

Topic

CVDpr
Starting Member

41 Posts

Posted - 2009-07-20 : 16:16:25

Hello there,i want to groupBy 3 fields: ID,SA, sum(amount)


ID | SA | Amount
-------------------
3   AD    23.45
3   AD      0.0
3   AD     5.75

and the result is ok:


ID | SA | Amount
-------------------
3   AD    29.2

the problem is when the SA dont have equal data the result is:


ID | SA | Amount
-------------------
3   AD    23.45
3   AD      0.0
3   AD     5.75

My Question is how to show blank '' in the field SA when the field dont have equal data so i can still group by ID,sum(amount)?:


ID | SA | Amount
-------------------
3          29.2

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 16:21:06

[code]DECLARE @Sample TABLE
(
ID INT,
SA CHAR(2),
Amount MONEY
)

INSERT @Sample
SELECT 2, 'AD', 23.45 UNION ALL
SELECT 2, 'AD', 0.0 UNION ALL
SELECT 2, 'BC', 5.75 UNION ALL
SELECT 3, 'AD', 23.45 UNION ALL
SELECT 3, 'AD', 0.0 UNION ALL
SELECT 3, 'AD', 5.75

SELECT ID,
CASE MIN(SA)
WHEN MAX(SA) THEN MIN(SA)
ELSE ''
END AS SA,
SUM(Amount) AS Total
FROM @Sample
GROUP BY ID
ORDER BY ID[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources