Grouping Issue in SQL - 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)

Grouping Issue in SQL

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2010-06-30 : 16:35:45

select -- f.giftacctno,f.giftacctnm,sum(f.giftamount)as Amt
giftacctno,
max(giftacctnm) as AcctName,sum(giftamount)as Amt,max(gifteffdat)as GiftEffDate

from gifts with(nolock)
where giftclass2 = 'Foll'
and giftclass1 = 'v'
and gifttype in ('bj','bv','b','gj','g','gv','yj','y','yv')
and gifttdeduc >0
and gifttender in ('c','cc','dd','pd')
and datediff(month, gifteffdat, getdate()) <= 2 --current month begin
and datediff(month, gifteffdat, getdate()) = 2 --current month end

--select distinct table_code,table_val
--from dbo.tender
group by giftacctno,giftacctnm,giftamount--,f.gifteffdat

Here is sample output
AccountNumber AcctName Amt Date
00111 Business 100 20100630
00111 Business 200 20100630
00111 Business 100 20100628
00112 Engineering 100 20100629

I want the output to be
AccountNumber AcctName Amt Date
00111 Business 300 20100630
00111 Business 100 20100628
00112 Engineering 100 20100629

In other words I want Business to be consolidated if the account number and Date are the same

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-06-30 : 16:41:21

what happens if you removed giftamount from the group by

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

SD_Monkey
Starting Member

38 Posts

Posted - 2010-07-01 : 03:25:07

you dont need to use the max...
use order by for sorting data value..

A maze make you much more better

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources